Looping a workflow through multiple files PLUS filtering results based on cell

[aehrenwo]

I have workflow I have finalized that currently works based on one master template (XLSX file) and a source file with Training information (assignments and completions).

If I wanted to have the workflow run multiple times based on a folder with different versions of the master template file what would be the easiest way to accomplish this? Is there a specific macro or would it be done using Dynamic Input? 

Let's assume for this example that the "training" source file would be the same for every iteration. However, I would also need to filter that data set based on a particular cell value in the template files that are being looped through. 

This is the first time I am trying use this functionality so not entirely sure where to begin.

Regards,

Adam

[JordanB · Accepted solution]

HI @aehrenwo

The macro you would want is a batch macro. 

A batch macro will process one record at a time. In your case this would be your different master template files. 

Luckily if your master template files are exactly the same the dynamic input will work for you. The only prerequisite for XLSX files is as follows:

You can use the directory tool to read in all your master template files from a folder, you will then get a 'FullPath' field. You will need to add the sheet name & Cell range to each of these files using a formula tool so when you read the files using the dynamic input Alteryx will know which sheet to read.

The syntax in the formula tool should look as follows:

[FullPath]+"|`Sheetname$[Cellrange]`" >>>>>> Cell range has to have a syntax of A1:B2

You can configure the dynamic input as follows:

The placeholder file can just be one of the master template files.

This article can provide more details around reading in multiple files with the same (your use case) or different field schema.

Best,

Jordan Barker

Solutions Consultant

[aehrenwo]

I am trying to figure out how the Output of this process would work.

I know I need the batch macro because i want each file to run individually ( not having the data all unioned)

However, for the output the template XLSX file I am using has a pivot table connected to the data tab that is being populated by the workflow but I need to update a generic version of the file and then do a rename of files based on the stored value in one of the fields in the source information.

So if there is field called FILE I wanted to update the template output file data tab and then save a copy as that specific file name --- Is this possible? 

Let me know if you need more info

Adam

[JordanB]

Hi @aehrenwo

If I understand correctly you want to add an output data tool and use this article and your FILE field to output to a specific data tab. 

Best,

Jordan Barker

Solutions Consultant

[aehrenwo]

the issue I am struggling are two fold:

1. I have a fully working macro assuming that only one "master template" file is being used to return a final result - I want to be able have a directory of similar Excel template files and run the same work for each one individually (they all have the same structure and tab labels"

2. How can a grab a different value from another tab on the template that is store in say cell B2 and use it as part of the filtering process in the workflow? 

If necessary I can try to create fake source data but i am pretty sure Alteryx should be able to manager this.

Regards,

Adam

[JordanB]

Hi @aehrenwo

I have attached a workflow which should hopefully demonstrate how you can do this. Your file with the value in the cell may need more parsing than mine, but the workflow should be explanatory enough to show you the foundation for the workflow.

Best,

Jordan Barker

Solutions Consultant 

[aehrenwo]

silly question - but does the workflow you provided assume I already have a Batch Macro tool installed in Alteryx? 

[JordanB]

Hi @aehrenwo

Yes. I presume you already have a macro to read in the different files and tabs.

So you can just add a formula tool within your current macro and feed in the cell value through another control parameter.

Best,

Jordan

[aehrenwo]

I currently only have a workflow that process one file successfully (without referencing the cell from the other tab yet).

Is there instruction somewhere on how to build out the macro? sorry for not specifying this... the whole concept of processing different copies of the same template file is eluding me. 

Thanks for your help

Adam

[JordanB]

Hi @aehrenwo

This thread will show you how to build a batch macro for your use case (Multiple Files & Multiple Sheets). There is an example workflow at the bottom of the article which you can download.

This article from @Ned blog will also give you some good guidance on Batch Macros.

Best,

Jordan Barker

Solutions Consultant