Loop through set of files

[jay_chang]

Loops in Alteryx are among the most frustrating experiences ever devised.  If there is anything that will cause me to move away from this tool, it's the fact that such a simple concept is so difficult to implement.  This seems to me one of the most common use cases for an ETL tool.  The answer to this (so I'm told) is a batch macro, which is another difficult concept to implement.

Here's my situation - I have a set of files.  They all have fun variations on their names based on how Windows auto-names downloaded files (i.e. file.xlsx, file (1).xlsx, file (2).xlsx, etc).  I need to grab all these files.  For each file, there is a column in the dataset which contains dates.  I need to find the max value in that column and append it to the data associated with that file.  This suggests a for loop as follows:

get list of all files matching criteria (ie, load file*.xlsx) into temp table

for each file in list

   read date column to get max

   append max(date) to file's dataset

   repeat until all files processed

How do I do this?  Thanks in advance for the help.

UPDATE: Forgot to mention - there can be zero user interaction required during the execution of this workflow, aside from perhaps updating the file path to the location of the downloaded files.

[LordNeilLord]

Hi @jay_chang

This is super simple to do in Alteryx (I presuming the schema is the same for each file)...no need for a batch macro here.

You need to use the wildcard input functionality (filename*.xslx) which will automatically import and union the files together:

I have attached an example

[jay_chang]

Thanks LordNeil, for the reply on a Sunday AM.  I realized I could do this via your method after I posted, but I am still wondering how I would set up a generic loop.  There is not good documentation that I can find that shows how you would construct a loop using batch macros.  Are you aware of any links like this?

[LordNeilLord · Accepted solution]

Hey @jay_chang,

It's sunday afternoon for me :)

So there are different types of macros for Alteryx, Standard, Batch & Iterative.

Standard Macros are where you want to compile a workflow into a "tool" so someone else can use the process without having to mess around with the workflow itself. Here's a good article: https://www.theinformationlab.co.uk/2017/03/07/alteryx-mms-standard-macro/

Batch Macros are where you want to group or chunk the data for processing: https://www.thedataschool.co.uk/rachel-phang/batch-macros-alteryx/

Iterative Macros are where you need to "loop" through data. These allow you to control the number of iterations in order to complete the task: https://www.thedataschool.co.uk/neil-lord/alteryx-macros-iterative/ (sorry for the shameless self promotion of my own blog post here)

Hopefully these will explain what each one does and helps to give you more understanding

[jay_chang]

Thank you for your help.