Iterative macros are one of the more advanced types of Alteryx macros; without knowing exactly how they operate and the best way to build them they can be a pain to make work. In this blog I’m going to show you how to successfully build an Iterative macro, and when you should build one. If you are new to Alteryx macros, Iterative macros are definitely not the place to start, I suggest you look into Standard macros first. It just so happens that I wrote a blog on Standard macros; you can find it here.

 

Why Build Iterative Macros?

 

So firstly, why would you ever need to build an Iterative macro? Well to answer that question you need to understand what an Iterative macro is. Iterative macros share all the same characteristics as a Standard macro, in that they add convenience and efficiency by packaging processes into one tool, however, they also add very specific functionality. An Iterative macro will run through all records passed through it then loop those records back through the macro until the process has been repeated as many times as specified or until a condition has been met. Now if you haven’t cottoned on yet, this is extremely useful when you need to solve problems that require a workflow to loop on itself until you achieve the desired output. The reality is, there are simply some problems that can’t be solved without an Iterative macro.

 

Requirements for an Iterative Macro

 

Iterative macros have similar requirements to Standard macros, however, there is one key difference. Iterative macros must have at least two Macro Output tools, one which will output the final result and the other which will output each iteration to then be inputted back through a selected Macro Input tool. I will demonstrate how to set this up in screenshots below.

 

Firstly, ensure you select “Iterative Macro” in the Workflow Configuration like so:

 

Next in your Interface Designer Properties select your “Iteration Input”, which will be the Macro Input tool you want iterations to pass through again, and your “Iteration Output”, which will be the Macro Output tool you want your iterations to come from.

 

There are a couple things to note here; the “Iterative Input” can also be the original input for your macro and if you look below “Iterative Output” you’ll notice that you can also set the limit on how many iterations you want to run in “Maximum Number of Iterations.

Once your workflow has met these conditions it can be used as an Iterative Macro, so without further ado lets have a look at how to build an Iterative macro from scratch.

 

How to Build an Iterative Macro

 

If we break down the process into simple steps building an Iterative macro is actually quite simple. I will be using a solution I built for Challenge 135 from Alteryx’s Weekly Challenges as an example throughout this process. In this example there are 1000 students and 1000 lockers, the first student opens all of the lockers and then each following student changes the position (opens or closes) every locker that is divisible by the student’s number (i.e. the second student opens/closes lockers 2,4,6,8…etc and the third student opens/closes lockers 3,6,9,12…etc) until we reach the 1000th student. We then need to find out which lockers are still open. If you want more context you can find the challenge here. You don’t necessarily need to understand the challenge itself, you just need to understand that this sort of process can’t be done without using an Iterative macro as it requires multiple iterations.

Build a workflow

You should always start by building the actual workflow first. Start with a normal Input Data tool or Text Input tool and work as if your goal is to complete one iteration. In this particular case there is no file to input so I start with a Text Input tool of my own making like so:

 

 

 

This is a logical starting point for the Iterative macro as we want to start at Student 1 who opened all of the lockers and work our way through to Student 1000, one student each iteration. The more astute of you may be asking yourselves how we can solve the challenge if I have only inputted one locker in the Text Input tool. The answer to this is another important lesson about how best to use Iterative macros; you don’t have to solve the whole problem inside of the macro, in fact (and this is true for this example) it is often much better to to prep the workflow for entry into the Iterative macro. In other words do as much as you need to before the Iterative macro in your workflow. Returning back to our example, I used a Generate Rows tool before I inputted the macro in order to create 1000 lockers, all of which would then be open and attributed to Student 1.

 

 

Now returning back to that initial workflow. We have our starting point, which includes the lockers, their position (Open) and the first student to go through the macro. We can now proceed to build a workflow that we will eventually turn into an Iterative macro. In this example the workflow is actually quite simple and looks something like this:

 

 

I start with the Text Input tool we saw earlier, then use a Select tool to change the type of the Lockers column to an integer (which actually isn’t strictly necessary as the Generate Rows tool can be set to do it before the macro, but makes the building process easier) and finally I apply a couple of formulas in the Formula tool. Without going into too much detail, I created one formula that changes the student number each iteration and another that determines which lockers that student opened or closed. The key to this is a constant called “Engine.IterationNumber”, which inputs the number of the iteration, starting at 0 (which is unfortunately counter-intuitive). You can find this constant here in the Formula tool:

 

 

So far we have a workflow that does exactly what we need it to; it changes the student each iteration by 1 and then decides which lockers to either open or close. All that’s left is to make it an Iterative macro and run it a thousand times right? Well, not quite, we need to decide how we’re going to output the final result – this is often the hardest part of an Iterative macro.

 

The Iterative Output and the Final Output

 

As we now already know an Iterative macro requires two outputs, one to run back through the macro and another for the final output. What we have so far is an output that we want to run through the macro again and again – so this must be our iterative output. Now is a good time to convert our input into a Macro Input tool and our output into a Macro Output tool. It’s important to note that anything going through our iterative output must exactly match the column names and the column types of the iterative input, otherwise it will not work. Sometimes you will have to add extra steps to do this.

We could simply connect the workflow as it stands to both an iterative output and a final output, but this would mean each iteration would be outputted one after the other, essentially unioning the results. Technically you could do this then add a couple of filters after the macro to get the results you want, however, that is inefficient and isn’t always applicable. For example, in this case we know that we want to run the macro for 1000 iterations, but what if we didn’t know how many iterations we wanted? If that were the case then we would need some sort of condition for the macro to stop iterating. By design an Iterative macro will finish when no more records pass through the iterative output. So we can create a condition for when the workflow flows through to the iterative output and when it flows to the final output. A Filter tool is perfect for this; in our example we know that once we reach Student 1000 the macro needs to stop. So when Student != 1000 is true then the iterations continue and when it is false the iterations have to stop. The workflow would then look something like this:

 

 

You may also need to add other conditions after identifying that the iterations should stop; in our example we only want open lockers to come out so we could do something like this:

 

 

We now have a fully functioning Iterative macro, ready to be inputted into a workflow.

 

Summarising the Process

 

So we went over the requirements of an Iterative macro, namely how to configure a workflow as an Iterative macro and how to set up the input and outputs. We then looked at an easy way to approach building Iterative macros; start with a normal workflow designed to be repeated and then set up the condition for the iterative output and the final output. One of the most important steps is to ensure our iterative output matches the column names and column types of the iterative input. We also touched on how you don’t need to do everything inside the macro, sometimes it’s better to prep the workflow before and after the macro. Hopefully after reading this blog you’re a bit more prepared to tackle Iterative macros. If you have any questions or comments, feel free to pop them below!

 

 

Patrick Breis
Author: Patrick Breis