Making macros accessible in Alteryx 3 – Iterative Macro

by David Sánchez

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 row-two fields

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:

first rows output[more rows following the same pattern]last rows output

Let’s have a look at the workflow:

workflow including iterative macro

Pretty straightforward workflow

What about the actual macro?

iterative macro workflow

connected loop

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:
engine iteration as operand

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:

  1. WHILE there are results not downloaded from my url, move to the next page and repeat until all results are downloaded → iterative macro.
  2. WHILE there are employees not matched to their managers, assign a manager id → iterative macro.
  3. WHILE there are transactions not assigned to a categoryiterative macro.
  4. 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


David Sánchez

Some Numbers @DS -- Reflection on 4 (fast) months

3 mins read

Fri 26 Jan 2018

Revelations – densification using bins and Index in Tableau. Case in point: normal distribution.

6 mins read

Sun 14 Jan 2018

Tableau for everyone

1 mins read

Tue 02 Jan 2018

Alteryx treating single character strings as boolean - when and how to fix it

1 mins read

Fri 22 Dec 2017