WeeklyAlteryxTips#62　Save data with the different schema to the different sheets in one Excel book – Part2

This post is the sequel of the “Save data with the different schema to the different sheets in one Excel book – Part1”. This tips is a little complex, so it is for more than intermediate level.

First, supposed that you have an item master and a receipt data as follows.

Item Master：

Receipt Data：

This post is about how to write them to one Excel book which has two different sheets by Batch macro.

Save data with different schemas to different sheets by batch macro

This is needed for a little complex procedure, so I would like to let you know the overview. First, you have to transpose the each data stream in advance. And then, you restore the data by Cross Tab tool inside the batch macro by data stream. Aftar that, write them into the each Excel sheets.

The workflow outside the batch macro is as follows.

The important point:

• Add the following fields for restoring the data in Batch macro.
  • The data stream identified field, which is used for distinguishing the data streams.
  • The record order identified field, which is used for restore the record order.
  • The field order identified field, which is used for restore the field position
• The multiple data streams are unpivoted into “Name” and “Value” fields by the Transpose tool. And then, union those data streams.

The workflow overview inside the batch macro is as follows.

In the batch macro, the data should be pivoted to restore the data. Using the batch macro, Alteryx decide the different schema each iteration. So, the schema of the restored data is not affected each other.

Note that when the data has the SpatialObj type or Blob type, you can’t use this way. However, the Excel file can’t handle these type data, so it is no problem.

The logic outside the macro

Pre-processing for each data stream

You have to process each data stream as follows.

1. Save the order of the records by the Record ID tool
2. Unpivot the data by the Transpose tool. The key field should be RecordID field
3. Add the ColumnID to save the column names and order by the Multi-Row Formula tool
4. Create the “SheetName” field to save the data

You have to do above procedure each data stream and then union them.

Let’s check the data. If you check the first 10 records, you can see like this.

The original data is put into “Name” and “Value” fields. Others are used for restore. You can create this pre-processing into macro if you like.

Get the sheet name list to save

You have to group the sheet name by the Summarize tool to get the sheet name to save.

Use the batch macro

You have to put the pre-processed data into batch macro.

If you like, you can pre-process many data streams and connect them to the Union tool.

By the way, if you like, you can use the grouping option on the batch macro, which is filtered the data to speed up the processing speed. In this case, you have to specify the “SheetName” field to the GroupBy Field.

Inside the Batch macro

Inside the batach macro, the workflow is very simple. The unpivotted data should be re-pivot, rename the field names and data type, and save the Excel file.

The important point is the combination of the Cross Tab tool and the Dynamic Rename tool. Because if you use the Cross Tab tool to restore the data whose column names have special characters, those characters would become “_”. So, I restore the data by ColumnID to hold the field order and exact field names.

Conclusion

• I have explained the way that is unpivotted the some data streams which have different schemas and then write the multiple Excel sheet by batch macro.
• Basically, you don’t have to create this complex workflow, however maybe there will be a situation where it will be useful.
• If you create any macros, you can use this simple workflow as follows.

Sample Workflow Download

The Next Blog Post is…

The next post will be the slight tips about macro input tool.