Combine multiple Alteryx outputs into one Excel workbook

[hodji]

I have an Alteryx workflow that combines multiple streams of information into 3 outputs for year, month and day.

For an example:

Year output

2017	% of x	% of y	% of z	Total % (x+y+z)
2016	% of x	% of y	% of z	Total % (x+y+z)
2015	% of x	% of y	% of z	Total % (x+y+z)
,,,,,	% of x	% of y	% of z	Total % (x+y+z)

the years continue back to 2005

Month output (same as year except broken down into months i.e. 2017-07, 2017-06, etc.)

Day output (same as year except broken down into days i.e. 2017-07-04, 2017-07-03, etc.)

I would like to have all outputs added into one Excel workbook with multiple tabs. So the year tab would contain the year output, month tab - month output and day tab - day output.

There is a large amount of data that Alteryx needs to go through to create the above 3 outputs. It takes on average 50 minutes of run time to run through the multiple tables and combine into the outputs.

So my questions are;

1) How do i go about creating a workbook with three seperate tabs?

2) Also after the initial workbook is created can the new data be appended? If yes, how would I set that up?

Thanks!

[NicoleJohnson · Accepted solution]

Add a formula to the end of each of the three separate paths in your workflow. Put the full file path name, differentiating each by the tab you want to put it in (for example, "Filename.xlsx|||Year","Filename.xlsx|||Month", and "Filename.xlsx|||Day"). 

Union the 3 paths together, then output to a single Output tool, designating in the section at the bottom of the configuration that you want to replace the initial output filename with the filename field created in your formula above, and unchecking the box to output that filename. 

This will basically put all your data into one stream, then output it into the 3 different tabs based on the file names you designated in each individual stream. And once your file is created with the 3 tabs, yes you can append to it each time, just make sure your output tool is configured to Append data instead of create new sheet or overwrite. 

Alternatively, you could keep your streams separate and output to the same file in each of the 3 streams and just designate which tab you want to output too, but you'll need to throw in a couple "Block until done" tools so that it doesn't error out while trying to write to the same file at the same time for the different paths.

Hope that helps! 

NJ

[hodji]

Hi NJ,

Thank you for the reply and example. I added the formulas (I named all 3 formulas as 'Tab'), merge and output to excel. After running the workflow, the formulas output everything correctly. After the merge all 3 formulas are merged together, years first, month second and days last, but there is an additional column that is created called Tab2. For the output to Excel, I copied your setup but the data (year, month and day) get added to on worksheet and then multiple worksheets are created that contain various layouts of the year, month and day data. I went back to verify the items and everything seems to be correct in the setup. I also played with the selections of the output to Excel but get the same results. Any thoughts?

Thanks,

[NicoleJohnson · Accepted solution]

Have you confirmed that your field types are exactly the same for all 3 separate workflows? i.e. If you had two of the three that were string fields and one an integer field, they wouldn't union together properly. Let me know if that solves the Tab2 issue? And then some screenshots of the configurations you've used and the odd results you're seeing would help diagnose the rest!

NJ

[hodji]

Hi NJ,

That was the problem, I fixed it and it works correctly. Is there a way to combine multiple Excel outputs that have different columns, or is it easier to perform that function in Excel?

Thanks,

[NicoleJohnson]

If you have different columns (such as ABC in the first sheet, ABD in the second, ABE in the third), when you union them together it will create all the columns in every sheet you're creating (ABCDE)... so I believe in that case you would be looking at either a macro scenario (where it could create each tab one at a time, with only those columns that contained data for that tab), or perhaps adjusting in Excel after the fact as you suggested (not ideal)... if you provide a couple screenshots or examples of how the columns differ though, perhaps we might be able to come up with something creative?

NJ

[logan07]

Where would you know where to put the block until done tool in your flow?