This is Part 4 of a four-part series covering macros in Alteryx. In this post, I will walk you through how to build an iterative macro in Alteryx.
An iterative macro runs through every record passed into it and then loops those records back through the workflow. This process continues until either a set number of iterations or a specified criterion is met.
In the following sections, I will walk you through a common use of iterative macros: allocating finite resources.
The Problem
You must allocate items from a warehouse to various stores around the country. Each store has an assigned priority, with 1 being the highest priority, 2 the second-highest, and so on.
Every store requires a certain amount of a set of items. Store “A” requires 47 of Item 1, 29 of Item 2, etc.
A central warehouse ships each item, but has a finite supply. For instance, there is a supply of 824 units of Item 1.
The task is to allocate items to each store, keeping in mind the priority in which stores are supplied. Depending on the supply of items coming from the central warehouse, it is possible that not all demand will be met.
Store Priorities
Store Demand
Warehouse Supply
Building an Iterative Macro
Our first step here is to prepare the data that feeds into the iterative macro. Once we have cleaned, prepared, and shaped the data as needed we can then focus on building the macro.
Prepping the Data
In the other posts in this series, I skipped explaining the non-macro steps in the example workflows. I did that because those steps didn’t contribute to learning how to build a standard or batch macro. Here, the prep stage is important enough to the example to go over it.
Macros can have multiple inputs. With iterative macros, I find it is easier if I can input one prepped and cleaned data set. Why is that?
Iterative macros require that we define an “iteration input.”. This is the macro input that gets changed for every iteration. With multiple inputs, it can be difficult to identify which input(s) should be the “iteration input.” By joining our data sets together before flowing into the macro, we only have one input and it must be the “iteration input.”
By inner joining each of the three data sets, we have created one “master” supply and demand data set. The data set now looks like this:
We now have a data set with each store, their required items and amounts (Required), and the supply of each item (Count).
Designing the Macro
Open a new workflow and drag a Macro Input tool from the Interface palette onto the canvas. Then, click a blank area of the canvas and select “Workflow” from the Configuration Pane on the left. From this menu, change the macro type to “Iterative Macro.”
With that done, click on the Macro Input again. Where it says “Text Input” click on “Edit Data…” to bring up a Text Input-like window. This is where you give the macro a template for testing the workflow. Go back to your original workflow, connect a Browse tool to the end, and copy the results. You can then paste them into the window we opened.
Now that we have populated our Macro Input with test data, we can proceed with building the rest of the macro workflow. As in previous posts, I won’t dedicate space to describing my workflow. Let’s skip to the end—the macro output.
Iterative Macro Outputs
Iterative macros have two outputs: the “iteration output” and the “results output.”
Results Output
In our iterative macro, we must filter our results to show only the highest-priority store. From the original 215 records, we have 9 records — one for each distinct item for the highest-priority store. With the stores filtered, we can then allocate resources and adjust the remaining supply with a Formula tool. These results are then passed into the “results output.”
There is nothing special about the configuration of the “results output.” The results of the macro pass through it. For each loop of the macro, Alteryx appends the results of the previous loop onto the current results.
Iteration Output
The “iteration output” is where everything else goes. In our case, since we need to allocate resources to each store, the data sent into the “iteration output” should decrease by one store’s worth of items for every iteration.
We originally had 215 records and passed 9 records into the “results output,” which means we have 206 records to pass into the “iteration output.”
The data passed into the “iteration output” is what is used as the input for the following iteration.
Interface Designer
Once we have configured the macro inputs, the general workflow, and macro outputs, we can configure the macro’s settings within the Interface Designer. You can open the Interface Designer by navigating to View → Interface Designer. With the Interface Designer open, click on the small gear icon to view the properties menu.
Iteration Input
Using the drop-down menu, select which input contains the records to use for the iterative process. In our case, since we only have one input, our selection is easy—Stores. Remember, that’s why we prepped the data before entering the macro.
Iteration Output
Using the drop-down menu, select the output that contains the records that will loop back through the iterative macro. Here, that is the output named “Iterate.”
Maximum Number of Iterations
By default, this is 100. This value is only important if it is possible for your macro to not run out of records. By setting this value to a reasonable number, you assure yourself that the macro won’t run forever when nothing is happening.
Output Mode
Because some stores have different numbers of items, it is possible for each iteration to have a different number of records. Each iteration will have the same columns. With those facts in mind, we select “Auto Configure by Name” to avoid Alteryx throwing an error.
Saving the Macro
Since we defined an output and iteration output, Alteryx automatically converts the workflow to an iterative macro. Go to save the macro, and you’ll notice the extension is .yxmc to indicate that it is a macro.
Alteryx has a default save location for macros, which can be changed at Options → User Settings → Edit User Settings → Macros.
Connecting to the Macro
Return to the original workflow. Navigate to the Macros tool palette and drag the macro onto the canvas. You’ll notice that the tool has one input and two outputs. Iterative macros typically have two outputs — one for the results and one for the iteration output.
Connect the J output anchor from the last Join tool into the iterative macro. To make the results easier to see, I used a Select and a Sort tool prior to viewing the results.
For this workflow, the output from the iteration output is nothing. That is because every store has a record for every item—even if no items were allocated to it.
The results show the fruits of our labors:
Notice that in row 55, store G didn’t receive any of Item 9 even though it required 30 units.
Wrapping it All Up
Over this four-part series, we reviewed standard, batch, and iterative macros in Alteryx. We learned how to build macro workflows, learned how to configure the Interface Designer, and how to connect to standard workflows.
Macros are challenging. If you keep at it, they offer powerful functionality that can simplify complicated workflows.
We hope that you gained a firmer understanding of macros in Alteryx throughout these posts.
Do you have more questions about Alteryx? Talk to our expert consultants today and have all your questions answered!
