What is an Iterative Macro?
An iterative macro is a process (or in the case of Alteryx, a list of tools and actions) that is repeated until a condition is met.
Why should you use them?
Iterative macros are effective as they allow a process to be easily repeatable without having to copy and paste the entire workflow. Consolidating the process as a macro also allows for: easier troubleshooting; allows the data to evolve per iteration; offers a method for a process to repeat when the number of iterations are unknown and can improve speed and efficiency in the overall workflow.
If you have done any programming before you can think of iterative macros as FOR / WHILE loops as it follows the same logic as to why you would use them.
How to create an Iterative Macro:
First you will need to identify what processes needs to be repeated. In the following example I will walkthrough a factorial calculator that uses an iterative macro.
A factorial is the multiplication of each integer between 1 and the selected number. For example the factorial of 5 (5!) will be 1 x 2 x 3 x 4 x 5 which is 120 so we can see how this has to iterate. The input file we will work with has a list of random numbers (not so big that it can't be returned) and an empty factorial column:
As there are no initial steps that need to be taken before the iterative process we can dive straight into the building the macro. Usually you should carefully consider what will be repeating and what won't as non repeating steps will likely break the macro.
To build the macro it is good practise to open a new workflow besides the main one as this will allow you to troubleshoot more easily when testing how the macro runs. Copy the data input tool from the original work flow and paste it into a new one.
Right click on the input tool and convert it into a macro input:
Next, to save confusion and make building easier click anywhere on the canvas so that the tool isn't selected, then in the configuration panel click on "workflow" and then set the macro type to "iterative macro":
When you convert your input tool into a macro input, the workflow type will automatically change to a "standard macro" we want to set it as an iterative macro early so we can use the inbuilt system to help us build our macro.
As the factorial field was left null I'll add in a select tool to make sure the data type is what I want it to be, in this case, int64. Now we will think about the logic of what needs to happen per iteration.
Understanding the iterative logic:
As a factorial works by multiplying integer numbers increasing by one up to the number set, each loop/iteration will multiply the previous calculation by the new iteration number. For example, if we want the factorial of 3, the first loop would be 1, the next loop would be 1 x 2 = 2 and the final loop would be 3 x 2 = 6. Here, 3 is the final iteration number as that's the factorial we want to find and 2 is the factorial value so far.
So in essence, for this example, we need to set the first loop for any number to be 1 and then for this number to update by being multiplied by each consecutive iteration number until the iteration number matches the number we want.
Building the iterative logic:
As we have made this workflow an iterative macro we can use the inbuilt "engine.iterationnumber". This is simply the iteration number in alteryx's engine that updates as the macro runs, where it counts the first iteration pass as 0. If you don't want to use this, you can create an iteration count at the beginning of your macro which increases +1 per run, however, I'll be working with the engine iteration number as it saves having to create these tools and logic.
The formula uses the first run to set the factorial for the numbers as 1 and for each pass after to multiply the factorial value by the value of the iteration pass.
Now we have to set a condition for how long the macro should iterate for, which in this case will be until the iteration number = the number:
Remember, the inbuilt iteration count begins at 0 so the "+1" is present in the formula and filter tools to account for this.
Creating the iterative process:
The current macro set up is the foundation of what needs to occur per iteration, but how do we make it iterate? We will first add macro output tools to both outputs of our filter tool:
Be sure to rename each output tool so you can easily identify which is which, in this case for the output connected to the false anchor I have labelled as "Loop" or "L" and the True anchored output as "Final" or "F".
From here either go on the top panel, and under "view" open the "interface designer" or press CTRL+ALT+D:
From here, click on "properties" which is the cog icon in the left hand panel and set up your iteration inputs and outputs:
There should be 1 option for your input and 2 for your outputs, hence why I renamed them earlier to select the right one (the looping one). You can also set the max number of iterations on this page too if you want. Once you have selected the correct options as above, save this macro and head back to the original workflow to use it by right clicking anywhere on the canvas, insert, macro and select your macro:
Once your macro is inserted, connect the original input tool to the input anchor of the macro, in this example we won't need to connect anything to the outputs other than browse tools:
Testing the macro:
It is important to realise within the macro workflow, running it will only test the very first run through - it will not iterate! Therefore, you can only check the very first pass to see it is working which in this case, it has passed the number 1 and the factorial of 1 out of true and every other number as false with a factorial of 1. So the first pass has worked successfully.
Running it in the main workflow will iterate through completely, and will output the results from the Final anchor in:
In this case it has worked as expected, however, if it did not it would be relatively easy to work through the logic within the macro flow.
This blog has covered the foundations of building an iterative macro and the key considerations regarding it e.g. what is iterating, how to check it, how to set it up.
Hopefully this will now enable you to build iterative macros of your own as they are an amazing tool to get the hang of for more complex use cases!