Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Loop through set of files

jay_chang
8 - Asteroid

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.

4 REPLIES 4
LordNeilLord
15 - Aurora

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:

 

Wildcard.PNG

I have attached an example

jay_chang
8 - Asteroid

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
15 - Aurora

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
8 - Asteroid

Thank you for your help.

Labels