community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Loop through set of files

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.

Alteryx Certified Partner

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

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?

Alteryx Certified Partner

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

 

 

Highlighted
Asteroid

Thank you for your help.

Labels