Loop through records and perform tasks individually

Hi there, I am trying to have Alteryx loop through a bunch of records and run a SQL stored procedure on each individual record until there's no records left. Once there are no records left, go on to do something else in the flow.

I'm not sure if I'm misunderstanding the capability of Iterative macros. I've done the interactive learning one which uses a single row that changes and outputs the value at the end. But I want a macro to go through multiple lines individually.

As a test, I have attempted to create a simple workflow that goes through a set of data and outputs that data to a csv file. It starts with a set of values with a row number (which will turn into RecordID generated once I get this working), then creating a variable for iteration number, as well as a filter to check whether RecordID = iteration number.

In the attached script, I expect all the lines to be output in the final csv file, but written one by one. I've tried a few different ideas on how to achieve this but it always just goes back to only processing one line. Can anybody tell me what I'm doing wrong?

Macro_LoopTest2.yxmd

Macro_LoopTest.yxmc

testOutput.csv

Iterative Macro

Accepted answers

Qiu

@jjoe
I feel it is more a Batch Macro for your requirement.

Iterative macro will iterave the output for updating, in you case, the whope 10 rows will be kept updating, rather than single rows.

Macro_LoopTest2.yxzp

All comments

Qiu

@jjoe
I feel it is more a Batch Macro for your requirement.

Iterative macro will iterave the output for updating, in you case, the whope 10 rows will be kept updating, rather than single rows.

Macro_LoopTest2.yxzp

CoG

This can definitely be done with both Batch and Iterative macros, although I'm inclined to agree with @Qiu that batch is the more appropriate way to go (iterative macros are best used when an iteration depends on what happened in the previous iteration like inventory allocation where how much inventory you have left depends on how much you have already allocated previously).

That being said, for the purpose of learning here is what is going wrong with your macro beginning with how an iterative macro works:

Starting from the the Macro Input your data will be processed, until it reaches one of two main end points (what you have called the "Exit" and "Loop" Macro Outputs). There is a third option which is any other end point like your Output Tool, but this third option is irrelevant to iterative macros. All data that reach the "Exit" Macro Output will be stored and Union-ed together until the iterative macro stops running, at which point this will function as the main output of your data from the iterative macro. When data reaches the "Loop" Macro Output, assuming its been setup correctly in the Interface Designer settings, that data, and only that data, will be sent back to your "Macro Input" Macro Input for the next iteration.

Herein lies the problem with the macro you provided. Your test begins with 10 records indexed by [ID], but your last filter, which is what is designed to isolate the single record you want to work with and output to the CSV file, is also what outputs to the "Loop" Macro Output. In other words, by the end of the first iteration, you have only one record to send to the next iteration, but that single record's [ID] matched the previous iteration number, and is thus filtered out on the second iteration of the macro, leaving you with only 1 record output before the macro concludes in the second iteration.

Without changing everything about your setup, this error can be immediately solved by moving your "Loop" Macro Output to the "F" anchor of the final Filter that way the remaining 9 sample records go through the macro. You can move the "Exit" Output somewhere else too to get useful output, but your single record problem will be fixed with this simple change.

As a final note, if I'm going to build an iterative macro that iterates row-by-row, I like the Filter Tool to be the first tool that goes through to isolate the single record that way I can immediately place the "Loop" Macro Output at the "F" anchor, and then I don't have to think about it anymore. Alternatively you can use the Sample Tool to get the first 1 row in addition to Sample Tool to skip the first row in parallel to achieve a similar outcome.

Hope this helps your understanding. Happy Solving!!!

Macro_LoopTest.yxmc

jjoe

This is fantastic! It does exactly what I want it to do once I modified it a bit to add SQL functionality. Thank you very much for the explanation. I will need to read up some more on batch macros / control containers to fully understand why this works. Appreciate the response

Qiu

@jjoe
glad it works.
@CoG
It is really nice of you to give such detailed comments.

jjoe

Hi @AndrewDMerrill

It seems that I indeed misunderstood how iterative macros work. Thanks for the detailed explanation, will help me wrap my head around it.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335