As a follow up to last week's tech tip, I was asked how do you export to different worksheets in the same Excel workbook using the Render Tool. Creating a table and using the Render Tool allows you to add a nice format to your data export. You may be aware if you use Alteryx to export to an tab in an Excel workbook using the Output Data Tool, the data is exported in a jumbled format. You may have to open it to expand columns etc.
Please note: I found this solution in this discussion in the Alteryx Community website > Link
You would set up your workflow as follows:
I start by having two datasets with arbitrary information such as below;
Next you add a Table Tool to both data sets in the workflow, with whatever default configuration it enables.
Then you add a Formula Tool after each Table Tool with the name you want your separate worksheets to be. I entered the following formulas;
Formula Tool 1
Formula Tool 2
Then combine these two data streams together with the Union Tool.
Next attach a Layout Tool next to the Union Tool. Set up this tool as follows;
Layout Mode: Each Group of Records
Check the box for SheetName
Orientation: Vertical with Section Breaks
Data Field: Table
Section Name: Sheet Name
Here is a picture of the configuration for this tool:
(You can always right-click on this tool and select Add Browse After to see what your layout looks like.)
Finally add your Render Tool to your workflow. Configure it as follows;
Output Mode: Choose a Specific Output File
Output File: Navigate to where you want the file to be saved and give it a custom name with an .xlsx extension.
Data Field: Layout
Separator: No Spacing Between Records
Paper Size: Custom Size
Size: - You may need to manually adjust this. Since my data was small I entered 3 X 3.
Margins: No Margins
Here is the image of the Render Tool configuration:
Now when you run the workflow, it will export both data sets to one Excel workbook. Click on this workbook in your results pane to open it.
It should appear as below;