When you want to save data with the different schema to the different sheets in one Excel book, you can control it easily with the Control Container that was implemented in Designer 2023.1. However, when you still use older version Designer or can’t use the Control Container with any reason, you have to do it with another way. I will show you all of those methods.
The same schema
When you save data with the same schema to the one Excel book, it is very easy. Enable one check box!(Just use “Take File/Table Name From Field” option on the Output Data tool as follows.) I introduced that way at the old post.
This will result in an Excel book that looks like this:
The different schema
When you want to save data with different schema, that way will not work. To use the option “Take File/Table Name From Field”, you have to merge them into one data stream. But it is very difficult to merge the datastreams into one correctly. For example, here is the Item master file.
This is a receipt file.
If you merge these files by the Union tool, the result would be as follows.
It is not what you need. And if you save that file to Excel book, the result is as follows.
But what you need is as follows.
Use the Control Container
If you use the Alteryx Designer 2023.1 and newer, you can use the Control Container for saving multiple datastream to one Excel book as follows.
If you use the Control Container, you must enable the AMP Engine. When you can’t use the Control Container or AMP Engine, you can use the next way(using the BUD tool). Anyway, this will result as follows.
Use the Until Block Done(BUD) tool
When you use the Block Untile Done tool, you need to create different workflows. Because the BUD behavior with no AMP Engine is different from using AMP Engine. For E1 Engine, it is vry easy. You just put BUDs before the Data Output tools. On the other hand, for AMP Engine, you need to divide the data stream using the BUD tool as upstream possible.
AMP Engine ON
With AME Engine enabled, the BUD tool behavior changes, so you doesn’t use the same way as AMP Engine Off. You need to somehow link the two data streams together. Basically, the Parallel Block Until Done in CReW macro does it, however it doesn’t work somehow with AMP Engine ON. So, I fixed it as follows.
If this way is failed, you have to use the Control Container which is the best way to manage the execution order.
AMP Engine OFF(Use E1 Engine)
With No AMP Engine, you can use a simple way as follows. You just put the BUD tools before the Output Data tools.
But when the workflow is complex, it might not work. In this case, you need to use the Parallel Block Until Done tool in CReW macro as follows.
Parallel Block Until Done doesn’t send data to 2 until finishing the downstream 1. But if it doesn’t work, you have to use the same workflow as AMP Engine ON as follows.
To tell the truth, there is another way using Batch macro. I would explain this way at the next post!
Conclusion
- I have explained to save data with the different schema to the different sheets in one Excel book
- With AMP Engine, you can use the Control Container.
- With NO AMP Engine, you can use the Block Until Done tool.
- If the above way doesn’t work, you can use the Parallel Block Until Done tool in CReW macro.
- There is another way using Batch macro. I would explain this way at the next post
Sample Workflow Download
The next post is…
The next post will be about using batch macro to save data with different schema to the one Excel book.
コメント