Iterative macros are the topic of this third post in a series (1, 2) to better understand macros in Alteryx. I am going to focus on the what and why — and keep it brief, as much better explanations on how to build them are already available (check Neil’s or Marc’s detailed examples).
What are iterative macros?
Iterative macros are the equivalent of WHILE loops in other languages:
WHILE a condition is not met, do something
In pseudo-code:
WHILE X condition is TRUE and Y condition is FALSE:
. DO Z
. IF condition X2 THEN BREAK
For example:
I have a single record with two columns. First column = 1, second column = 100; and I want to obtain a table with n rows such that each row is the result of adding X to the first column, and subtracting X to the second, until the first column is greater than the second.
Input: single record with two fields
So, again, in pseudocode:
WHILE m <= than value:
. Add 1 to m, subtract 1 from value. Also, add another column with the number of the loop/iteration.
And the expected output is:
[more rows following the same pattern]
Let’s have a look at the workflow:
Pretty straightforward workflow
What about the actual macro?
The same workflow as above, connecting the output and input of the iterations
Pretty easy as well: add 1 to m, subtract 1 from m…etc…
I find it annoying that Alteryx, being so visually oriented, does not connect the Iterative Output and the iterative input in the loop:
Now we can just edit the macro and replace “1” with, for instance, the Engine.IterationNumber (aka the number of loops), so that, in pseudocode, will do:
X = 1
WHILE m <= value:
. Add X to m and subtract X from value.
. Increment the value of X by 1
Which will then return:
Other Uses
These above are two silly examples that, hopefully, will illustrate the rational of the WHILE condition. Anytime you’d use WHILE to explain what you want to do in plain English, you are most likely able to use an iterative macro:
- WHILE there are results not downloaded from my url, move to the next page and repeat until all results are downloaded → iterative macro.
- WHILE there are employees not matched to their managers, assign a manager id → iterative macro.
- WHILE there are transactions not assigned to a category → iterative macro.
- FOR each URL in my input table, download the content → BATCH macro!!!
Last, but not least, do not forget to check some of the most common pitfalls when creating an iterative macro.
Any thoughts or comments, reply here or find me at @dsmd4vid