Solving Your Business Problems Using Alteryx: Iterative Macros

9 min read

 

This blog aims to introduce iterative macro, a powerful Alteryx feature that can solve complex business problems. Firstly, we will establish what an iterative macro is and why it is important. Secondly, we will discuss some common use cases for iterative macros. Finally, we will demonstrate the power of iterative macros by solving a supply allocation problem.

 

Content

1. What is an Iterative Macro
2. Use Cases for Iterative Macros
3. Iterative Macro in Action: Supply Allocation

 

 

1. What is an Iterative Macro

An iterative macro is an advanced type of macro, which like standard macros, is very useful for building analytic processes that are performed repeatedly. While a standard macro runs the workflow just once, an iterative macro can continue to run until a certain pre-set condition is met.

An iterative macro keeps iterating through the workflow until a pre-set condition is met, after which the iterative macro exits the workflow.

To put it simply, an iterative macro is just like a robotic vacuum cleaner. In this case, the condition is set to cleaning an entire room. While the floor area hasn’t been fully covered, the robot will keep moving aound the space iteratively. And as soon as the entire floor area has been covered, the condition is considered met and the robot exits its cleaning workflow.

Like robots, the key strength of iterative macros is that once set up, they can automatically perform tedious and complex tasks, reducing both resources spent and the chance of human error.

 

 

2. Use Cases for Iterative Macros

Iterative macros are the most useful in problems that involve iterative calculations or operations based on a condition, for example:

• Financial Calculations
  • Compound returns at the end of N periods (Iteratively growing returns at each time period, until the Nth period)
  • Loan repayment period (Iteratively reducing the loan balance until balance equals 0)

• API Pagination 
  • Iterate through a list of endpoints, until there’s no more to load. (See this post for more details)
• Allocation of Finite Resources
  • Supply Allocation (Iteratively allocating stocks to different stores, until all stores have been processed)
  • Sales territory assignment (See this post for more details)

 

 

3. Iterative Macro in Action: Supply Allocation

The Problem:

COVID-19 is disrupting supply chains on a global scale. Major auto producers have especially felt the adverse impact caused by the pandemic induced labour supply shocks, delayed custom clearances, and shortage of key components.

Imagine we are working for a major auto producer with very limited supplies, meaning that they do not have enough stock to meet the demand from all dealers. The company wants to prioritize on their most important markets and stores. It will first satisfy the demands from the highest priority stores, and then move onto the lower priority stores…until all stores have been restocked (or at least informed with their allocation for this period when allocation is zero). Assuming that each store is assigned with a priority level, with “1” being the highest, and “2” the second-highest and so on.

 

The Logic:

This sounds like an iterative problem, with the exit condition being all stores having received an allocation (even if the allocation is zero).

• We should focus on a single priority level at each iteration.
• In the 1st iteration: 
  • Focus on stores of the highest priority (priority = 1)
  • If Total Supply >= Total Demand from all level 1 stores, then there’s enough stock to satisfy all their demand
  • If Total Supply < Total Demand, then a decision rule is needed：
    • Randomly allocate stocks among these stores, or
    • First come first serve, or
    • Allocate stocks proportionally to their relative demand. (I believe this is the most reasonable option)
  • Allocate stocks according to the chosen decision rule, and recalculate the Remaining Supply (Total Supply – Stocks Allocated this iteration).
• Move onto the 2nd iteration:
  • Focus on stores of the 2nd highest priority (priority = 2)
  • Repeating the above steps…
• Until there’s no more stores to process.

 

The Solution:

Step 1: Create a Macro Input

1. From the Interface Palette, drag the Macro Input Tool onto the empty canvas.
2. In the Configuration window for Macro Input, click on Edit Data, and populate the table with a dummy data set for testing (as shown below).

 

 

Step 2: Set the Workflow Type to Iterative Macro

1. Click on a blank space on the canvas to bring up the Workflow Configuration window. 
2. Select the Workflow tab, and select Iterative Macro.

Note: Unlike Batch and Standard Macros, we need to manually tell Alteryx that we are building an iterative macro. Without this step, we will not be able to configure the relevant properties in the Interface Designer.

 

 

Step 3: Find the Top Priority in the Current Iteration

1. Using the Summarize Tool (in the Transform Palette), find the Top Priority in the current iteration. Since 1 is the highest priority, here we take the Min (minimum) action on the Priority field.
2. Append the Top Priority value to the data table as a new field.

 

 

Step 4: Filter for the Stores of the Highest Priority in the Current Iteration

We are using a filter here, because at each iteration, we want to allocate supplies to stores with the highest priority. And only when we have stocks left over, do we move onto stores of the next highest priority in the next iteration.

1. In the Filter Tool, set the condition to [Priority] = [Current Top Priority].

 

 

Step 5: Calculating the % Total Demand as a Basis for Later Decision

1. Use the Summarize Tool to calculate the total (sum) demand of all stores of the highest priority in the current iteration.
2. Append the total demand to the data table.
3. Use the Formula Tool to create a new field called “% of Total Demand at Current Priority”, which is calculated as each store’s demand divided by the total demand of all stores of the highest priority in the current iteration.

 

 

Step 6: Establishing the Allocation Decision Rule

1. In the same Formula Tool as Step 5, create a new expression.
2. In the expression, select the Allocation field to update.
3. Input the logic as shown below.

 

 

Step 7: Keeping Only the Relevant Fields and Passing this Iteration’s Data onto the Macro Output

1. In the Select Tool, deselect the fields that we no longer need (as below).
2. Drag the Macro Output Tool from the Interface Pallet onto the canvas. We will name this Macro Output as “Output-Exit” for clarity.

 

 

Step 8: Updating the Remaining Total Supply

1. In the Formula Tool, update the Total Supply field by subtracting it with the Demand allocated this iteration if Total Supply was above Total Demand at Current Priority. Otherwise, set the remaining Total Supply to 0 (since if that’s the case, then all supplies would have been used up in Step 6)

 

 

Step 9:  Keeping Only the Relevant Fields and Passing the remaining Data onto the Iterative Macro Output

1. In the Select Tool, deselect the fields that are non-existent in the Input Macro (shown below).
2. Drag the Macro Output Tool from the Interface Pallet onto the canvas, this will become the Iterative Macro Output. We will name this Macro Output as “Iterative Output” for clarity.

 

 

Step 10: Configuring the Iterative Macro

1. CTRL+ALT+D to open up the Interface Designer window. Click on the gear wheel icon to go to Properties.
2. Always add an informative and interesting Custom Icon!
3. Select our Macro Input as the Iteration Input, and the “Iterative Output” Macro Output (not the “Output-Exit” one) as the Iteration Output.
4. CTRL+S to save the Macro.

 

 

Final Iterative Macro in Action

Finally, we can test our Iterative Macro by inserting it into a new workflow. As we can see below, our Iterative Macro has successfully allocated available Total Supply to different Stores according to their level of Priority!

You can zoom in or enlarge the image to see the results more clearly.