Iterative Macros in Alteryx: A Simple Example

In this blog, I'll walk through what an iterative macro is and provide a simple example for how you might use one. I'll assume you're already familiar with the general concept of a macro.

What is an Iterative Macro?

An iterative macro runs all inputted records through a process, checking whether they meet a condition after going through said process. The records that meet the condition will flow into an "answer" output, while the records that do not meet the condition will flow back into the initial input. Basically, it's a loop.

This can be a great way to operationalize sorting through tons and tons of records in cases where you would need to perform the same operation on them over and over. Iterative macros can save a lot of time.

Let's walk through the above example to see how it works.

Example: Interest Rate Calculator

In this example, imagine we want to know the number of years it would take for a particular amount of money, say $500, to reach $1,000 at an annual interest rate of 5%. By running that value through an iterative loop and getting a count of the iterations needed to reach the goal we can achieve this.

Macro Input

For this example, we're using a very simple text input and converting it to a macro input.

Our macro input is simply a text input with a single column and one record. You can right click any input tool to convert it into a macro input. Doing this will automatically configure the workflow into a standard macro, as evidenced by the Workflow Configuration Pane:

Calculation

Next up is our "process," which somehow changes the incoming records. Here we're using a formula.

This formula tool to updates our "Investment" field and a new "Year" field once every iteration.

This simple formula is performing two calculations. The first is taking whatever records are in our "Investment" field (in this case just one) and adding 5% to it. To elaborate, the first iteration will be 500*(1.05) = 525, the second iteration will be 525*(1.05) = 551.25, and so on.

The second is adding a new column, "Year." It uses a special constant called Engine.IterationNumber - a count of the iterative loops that have occurred so far. It adds up how many years have passed in our hypothetical scenario of annually compounding interest. Here I have the formula adding +1 to this constant because it starts counting from 0 (without that our $500 would be implied to have matured to $525 over zero years!). Another thing about the constant: to have access it, the macro needs to be set to "Iterative Macro" in the Workflow Configuration Pane. If our macro wasn't iterative, there would be no  iterations to count after all.

Filter and Macro Outputs

Records only pass through the True path if they've "matured" to $1000 or above. It's at that point that we can see how many years it took to get there.

Next is the filter. It's rather simple. We only want a record to pass through the True path if it has met our $1,000 goal. If it has not yet, it should pass through the False path. The records that pass through the False path should then be looped back to the beginning for another year of accruing 5% interest, while any records that pass through the True path should "exit" the macro and be outputted.

Now, how do we tell Alteryx to do this? We need to make use of something called the Interface Designer, locatable in the View menu:

From there we navigate to Properties and ensure we configure the iteration output to be the iteration output, as well as the input as the iteration input. It is helpful to have named the two macro outputs appropriately beforehand to be certain you're selecting the appropriate ones here.

From there, you're ready to go. Save the iterative macro to a safe place. You can then right click and insert it into a workflow by navigating all the way to the bottom of the right click menu and selecting it from your list of macros. Note: it's important that whatever data is coming into the macro here is formatted the same as the data we put into the macro input tool earlier. If it's the exact same data as it is in our case, there should be no issue.

Running this workflow should produce a single record with the number of years it took for our $500 to reach over $1,000, which is 15. The power of compound interest cannot be understated, but remember what Ben Franklin said: "An investment in knowledge pays the best interest."

Author:
Lex Devlin
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2025 The Information Lab