topic Re: Looping a workflow through multiple files in Alteryx Designer Desktop Discussions

Looping a workflow through multiple files

Paul_L — Thu, 24 May 2018 02:40:53 GMT

Hi there,

I've a set of data files with a date prefix. I'd like to process each file individually fully through a workflow (carrying out Market Basket Analysis) and then write out individual output files from the analysis along with the date prefix. My ideal user input would be a start date and number of days (e.g. 30/10/2016 and 4).

I've seen various bits of generic advice referencing iterative macros, dynamic inputs and batch macros, but none seem to cover it. I feel like it should be straightforward, but I'm struggling to achieve it. The files will be very large, so I need to process each file individually.

Example filenames:

30_10_2016_inputs

31_10_2016_inputs

01_11_2016_inputs

02_11_2016_inputs

Each file will contain many rows of items along with their corresponding order ID to run through the inbuilt Market Basket Analysis R tool. I want to export the rules from the MB Inspect tool to files named:

30_10_2016_output

31_10_2016_output

01_11_2016_output

02_11_2016_output

Thanks in advance,

Paul

Re: Looping a workflow through multiple files

Paul_L — Mon, 19 Dec 2016 16:57:30 GMT

Forgot to say, I'd also be happy for a single output file with a column containing the date, but I suspect that's harder to achieve.

Any help gratefully received,

Thanks Paul

Re: Looping a workflow through multiple files

DataBlender — Mon, 19 Dec 2016 18:22:36 GMT

Hi @Paul_L

Here's a workflow which contains the following elements which I think you'll need for what you're trying to accomplish.

1. Read in directory of filenames

2. User selects date and number of days

3. Generate rows to identify required filenames

4. Dynamic input to read in relevant files (NB. Output File Name as field has been configured in tool)

5. Perform batch macro based on each file's data only (in this case calculating an average).

6. Outputting to different file names based on the values in a field by configuring output tool to Take File/Table Name from Field

(Edited response to include user input for date range)

Re: Looping a workflow through multiple files

Paul_L — Tue, 20 Dec 2016 16:49:37 GMT

Hi DataBlender,

Many thanks for your help, really appreciate it. In the end I found what appears to be a simpler solution as it does not need a dynamic input. After the Generate Rows tool, I placed a formula tool to create the full filepaths of the input and output files, appended with the dynamic elements:

\"+DateTimeFormat([Date],"%Y_%m_%d")+"_input.csv" & \"+DateTimeFormat([Date],"%Y_%m_%d")+"_output.csv"

I then placed my full workflow for the market basket analysis inside a batch macro and used a control parameter along with action tools (update input data tool & update output data tool) to update the input and output files for each iteration.

As the inputting and outputting of the files are within the batch macro there's no need to pass the filename through the workflow which simplifies things massively.

Thanks again,

Paul

Re: Looping a workflow through multiple files

ckestler — Fri, 06 Oct 2017 17:52:24 GMT

Hi Paul,

I'm am trying to do something similar. Would you be able to post your workflow and macro? Thank you!