ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a time due to scheduled maintenance on Thursday, April 22nd. Please plan accordingly.

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
fvilelabr
Alteryx
Alteryx

I recently had a session in our Virtual Solution Center when a user asked me about the possibility to simultaneously output multiple excel files with multiple sheets, however, with a peculiarity: he needed to output different files based on column X, and each file needs different sheets based on column Y. 

 

For example:

 

Picture1.png

 

Code: Column used to create the output files: Output_1.xlsx ; Output_2.xlsx

Separator: Column used to create the sheets: Output_1.xlsx with 3 sheets (A, B, C) ; Output_2.xlsx with 2 sheets (A, B)

 

The Output Data tool is used to write the results of a workflow and can be used to write distinct results for each value in a selected field. So, it's possible to create different files based on field or multiple sheets in the same file based on field.

 

Two additional resources about this topic:

Output to separate Excel files 

Output to Multiple Excel Sheets in Alteryx 

 

In order to have two simultaneous actions we must use a more advanced technique: the Batch Macro.

 

giphy  The focus of this article will not be about building macros. But don't worry, look at this article or these Interactive Lessons in order to know everything you need know about them 😉

 

We are going to create a batch macro that will create a file with different sheets, using the field [Code] as the Control Parameter to create multiple files, at the same time.

 

Step 1 - Add a Macro Input tool

 

Picture2.png

 

 

Step 2 - Add and configure the Output Data tool:  We must to set up this tool in order to create multiple sheets based on Separator field.

 

Picture3.png

 

 

Step 3 - Add and configure configure the Control Parameter tool and Action tool:

 

Picture4.png

 

Setting up the Action tool:

 

1- Select Update Value with Formula:

 

Picture5.png

 

 

2 - Create a formula to update the file name based on the field of the Control Parameter:

 

Picture6.png

 

 

Step 4 - Save it!

 

Now we have our batch macro configured to receive the input data and to use the [Code] field to create multiple files with different sheets at the same time.

 

Using the Macro

First, drag and drop a Summarize tool in order to eliminate duplicate [Code] values. Next, set up the [Code] field in the first anchor of the macro: Control Parameter. Finally, set up the [Code] field in the second anchor of the macro: Group By and Input Group By

 

1 - Drag and drop a Summarize tool:

 

Picture7.png

 

 

2 - Set up the [Code] field in the first anchor of the macro: Control Parameter:

 

Picture8.png

 

 

3 - Set up the [Code] field in the second anchor of the macro: Group By and Input Group By:

 

Picture9.png

 

 

Et voilá! Run your workflow and take a look at the results 😃

 

Picture10.png

 

 

Note: Attached is a sample workflow for demonstration purposes, and not for use in a production environment.

 

Banner image credit fotokrug.