Alteryx has a great many ways to output data, not the least of which is the spreadsheet giant Excel. Almost everyone in an analytics space has dealt with data in Excel in some way shape or form in their career, and many use it as their go to format to deal with data or present reports. There are many different ways to output your data to Excel in Alteryx such as:
- Outputting to multiple tabs with identical schemas in a single output file using the Output Tool (http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-to-multiple-sheets-within-an-Excel-fil...)
- Outputting to multiple tabs with differing schemas to a single output file Using the Output Tool (http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Multiple-Excel-Files-to-One-Excel-File-with-M...)
- Outputting completely separate Excel Files with identical schemas using the output tool (http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-to-separate-Excel-files/ta-p/1247)
- Output different tables to multiple tabs in Excel (via the Render Tool) (http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-different-files-to-multiple-tabs-in-an...)
This article will explore how to output tables of differing schemas in their own tabs in multiple Excel file via the Render Tool. This addresses the specific use case where you have data in two separate tables that are very different in structure. However, in this example we’ll assume that the data in those tables is categorized in to different regions (North, South, and West). The end result is that you want to have 3 Excel files (one for each region), and you want to have two tabs specifically named in each of those excel files (one tab for each table of a unique structure that you want to output).
The first step is to complete all of your data processing, once we move to the Reporting Tool set you will lose much of the flexibility to manipulate your data once your tables are converted to “Report Snippets.”
To create a Report Snippet for each of your tables, drag down a Table Tool (located in the Reporting Tool set) on to the canvas. Assuming you have data from each region in all of your tables, you will want to be sure to select the “Group By” function in the Table tool. Here you will select the field that contains the Region information. Thiswill tell the Table Tool to output completely separate tables (one for each region). These separate tables will be the basis of creating unique .xlsx files.
Following the Table Tool, you can add in a Formula tool and create a new field that can be used to name the individual tabs you are sending your data to in the desired Excel file. This is a best practice when using this method to create multiple tabs and will help the end user intuitively know what tabs contain what data.
Now you canuse a Union tool to bring everything together. At this point you should have a field you chose to group by in the table tool (in the case of the attached example it will show up as FileName), the Sheet name that you created in the Formula tool, and a Table field which contains the report snippet for each unique table you have in your data.
The last two steps are to configure a Layout tool and then finally the Render Tool. In the Layout Tool, select the Orientation option of “Vertical with Section Breaks.” This will tell Excel that you want the data to output to separate tabs. Now you can click on the “Table” entry at the bottom of the tool and mark the Section Name check box, and select the field Sheetname that we created earlier in the Formula tool.
This will tell Alteryx how to name each of the tabs inside each of the unique Excel files you are creating. The final step is to configure the Render tool. In the previous step I have told Alteryx I want to have multiple tabs and that they should be named based on the field “Sheetname.” In this step we are going to tell Alteryx that we have multiple files we want to output, and they will be named according to the field “FileName”
Choose the output mode for “Choose a Specific Output File” and then designate a place for that file to go. Then check the Group Data in to Separate Reports and choose the FileName field. Here you can make some selections regarding how you would like your files to be named. You can choose to append the Group name (in our case “FileName”) to the test.xlsx to create testNorth.xlsx, or you could prepend the FileName or you could even replace test.xlsx altogether with the FileName field.
Make your selection and choose your Data Field to be Layout with no spacing between records.
Note: Excel outputs from the Render tool will try and accommodate the size of the sheet of paper the Render is trying to write to. Therefore, if you have particularly wide tables, it is best to set the Orientation to Landscape, and alter your paper size to accommodate your large tables. Otherwise fields or field names may get truncated.
The attached workflow was built in Alteryx version 10.5 but was adjusted to work in version 10 and above.