This article is part of the CS Macro Development Series. The goal of this series is to communicate tips, tricks, and the thought process that goes into developing good, dynamic macros.
It provides a technical walkthrough of building an iterative macro in Alteryx Designer. It assumes prior experience with standard macros and a working knowledge of the Designer’s interface and tools.
If you are new to iterative macros or macros in general, go to the Alteryx Academy to learn about macros:
What is an Iterative Macro?
An iterative macro is designed to repeat a process until a certain condition is met. For example, you might want to remove items from a dataset one by one until only those meeting a specific criterion remain. Iterative macros allow you to build loops inside your workflows—something regular workflows don’t support.
Each cycle of the macro is called an iteration, and the macro continues to run until no more data is passed to the “iteration output”.
When I started out at Alteryx, I completed a series of exercises to help me ramp up on iterative macros. One of these was the “knapsack problem” below:
There are 5 boxes of varying weights and dollar amounts - which boxes should be chosen to maximize the amount of money while still keeping the overall weight under or equal to 15 kg?
From a challenge standpoint, which combination of box/boxes is most optimal if you were only allowed 1 box in the backpack? 2 boxes in the backpack? 3 boxes in the backpack? or 4 boxes in the backpack?
This is a classic optimization problem. In Alteryx, the most straightforward way to go about investigating this problem would be to generate combinations of these items using a series of Append Fields tools.
You would need one additional Append Fields tool for each number of objects you want to investigate. The above creates two-item permutations, and tacking on another Append tool to match up the original boxes to the output of the first Append Fields would give you three-item permutations.
You would also have to filter out the repeats in this case – after all you cannot have two of the same box placed in the knapsack. In the end, this method requires you to combine streams where you look at 1,2,3,4, & 5 box combinations. It would not be a lot of work, but it also would not be dynamic – that is to say, if we suddenly added a sixth box, our workflow would not be able to handle it properly.
In computer programming, a recursive solution may be best – it iterates until all combinations have been generated. For a number of reasons, recursion is not possible in Alteryx. However, the next best thing to recursion is a “looping” solution, and this is possible with an iterative macro. As explained above, an iterative macro runs over and over until it either reaches a set number of iterations or until some logical condition is met. After an iteration has completed, its output may be looped back into the input of the macro for the next iteration.
In this case, we could use an iterative macro to essentially replicate the Append Fields logic as many times as needed. One-character length strings can be used to represent items, and concatenated strings to represent combinations.
A Filter tool before the macro's output would check each time to see if the length of the combinations was the same as the number of items. If it was the same, it would know that all appends have been completed and output the list of combinations to the “Done” anchor (Shown here with the letter "O"). If not, it would feed the results out of the “Loop” output ("L"), and run another iteration. Once no data was being filtered through to the “Loop”, the iterations would cease.
You don't need all the records to be "Done" all at the same time. You can output any number of records during an iteration. These results will just be stacked together at the end of the final iteration based on theOutput Mode you have configured in the Interface Designer window(View > Interface Designer). TheInterface Designer is very important for configuring an iterative macro because you need to tell Alteryx which output it should loop back (Iteration Output), and which input should receive the looped output (Iteration Input).
You must also specify an upper limit on the number of iterations it can run so that you do not accidentally create a macro that runs forever. For the Combination macro, I set this limit at 25, since at this point it will be generating 33,554,431 combinations… which is excessive.
I wrapped the entire iterative macro up in another macro so that I could include some configuration options and a Message tool to prevent more than 25 items from being fed into the iterative macro to begin with. A Count Records macro is used to find how many records there are, and this number is appended as a field to the data. The Message tool is configured as "Error - and Stop Passing Records Through This Tool" in order to halt execution if the “count” field is too high.
The end result is the Combination macro. It can take a field and return all combinations of the items inside.