Creating a dynamic workflow with multiple tabs

[cjefferies]

Hello! - I am fairly new to Alteryx and struggling a bit with a workflow I'm trying to create. I will try to describe the desired output as best as I can below:

The goal is to use a singular input file (with consistent columns each time it's generated, but with different data) and create multiple output files (one for each unique identifier in the first column of the input file). 

These output files should have two tabs: 1. a simple pivot with the unique identifier/account name/product family in the first few columns + remaining columns are end dates with total dollar values aggregated for each end date by product family. 2. the exact same format as the input file, but only for the unique identifier used in the first tab (essentially the input file just filtered for one unique identifier) 

This issue I have been running into is when generating tab #1 - the output file contains all end dates from every unique identifier and there are numerous blank columns in each that are not used. I'm also having trouble combining the two tabs into one file.

Please see below for an example input file as well as desired output. The data to be used is confidential and I have tried my best to change & simplify for the exercise. Thanks so much in advance!

[griffinwelsh]

If I understand your request correctly this sample should work for you. You can use a batch macro to process each identifier one at a time and write each to its own tab on the spreadsheet.

[binuacs · Accepted solution]

@cjefferies one way of doing this with the batch macro

[cjefferies]

@binuacs

Thank you for the quick response! Quick question as I'm new to Alteryx. But what should go in the formula field where you have "//update your path here'."? 

I'm assuming this is a file path to either my input file or output folder, but please correct me if I'm wrong. 

Thanks!

[binuacs]

@cjefferies Yes, it is for the output path, if you want your files in a specific path you can update here, else all output files will be saved in the same location where your workflow saved

[cjefferies]

Hi @binuacs - 

Quick question - but can you explain what the goal of the Formula tool is in this workflow? Having a hard time understanding what should be going in this expression. Apologies for the inconvenience. Thank you

[cjefferies]

@binuacs 

Hello - this is a reply to an earlier thread you assisted with and helped me tremendously, thank you so much. 

I have one additional question if you had a moment. 

If I were to use this batch macro and create multiple output excel files into a new folder - is it possible to use the method of overwriting an existing excel file to preserve formatting? I've used this method plenty of times on workflows that contain a single output file however was not sure if there was a way to do this if the output file name is changing each time. 

For example - if I were to preformat an excel file with three tabs (that are named the same and have the same formatting in each output) with bolded headers, column width, etc --> would this only work for any files that contain the same name? 

Please let me know if I did not explain this well enough. My assumption is I will need to create a new batch macro but am not totally sure. Thank you.

[binuacs]

@cjefferies the formula is to create the file name and the sheet name, as per your requirement you need each files to be created based on the field "unique identifier" and two sheets one for the summary details and other for the data, for this purpose you need the formula tool

'./'+toString([Unique Identifier])+'.xlsx|||Sheet1' -- in this path the output file will be created where your workflow saved, if you want to write in a different location then you need to update the formula tool and mention the path here. for example you want to write the output in C:\user\downloads then you need to update the formula tool path as

"C:\user\downloads\"+toString([Unique Identifier])+'.xlsx|||Sheet1'  -

[binuacs]

@cjefferies yes you can use the same settings here as well, just make sure that you need to mention the range in the formula tool along with the path name

suppose you want to write the output in a specific range then update your formula tool

"C:\Users\bakerc12\Desktop\Output"+toString([Unique Identifier])+'.xlsx|||'Test1$B11:B13'"

[cjefferies]

@binuacs 

Thank you so much for the help.

Another question, but would this method work even for 100+ output files using the batch macro above? Previously I have created a desired output that is fully formatted, named correctly in order to direct my output data node to it, and saved it into my desired output folder, then used the "Overwrite Sheet or Range" and "Preserve Formatting on Overwrite" options. However this has only been when there is one output file and I can safely create that. If there are 100+ would the formula tool ensure that each output file is formatted exactly like the example I provide and save?