Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
What if I told you there was a way to run the inside of an iterative macro iteration by iteration?
I recently started using an incredible design pattern from the legendary @patrick_digan. It has been game-changing for me! I got Digan’s blessing to share it with all of you. Are you ready to have access to any iteration of an iterative macro?!
It’s all possible through intermediary yxdbs.
Macro Setup
Step 1: Build the outer workflow up until the location where the iterative macro will be.
I’ll be using Weekly Challenge 14 as my iterative macro example in screenshots. In the instructions below, I will separate certain instructions for those of you modifying an existing macro vs creating a new macro.
Step 2: Determine which data will iterate through the macro.
Existing macro: Open your macro. Go to View > Interface Designer > Properties to see which Macro Input represents the iteration input.
New macro: Open a new workflow and save it as an iterative macro. Think through which data stream will be your iteration input and output. (New to iterative macros? Check out resources here, here, and here.)
Step 3: In your outer workflow, output the starting data for your iteration to a yxdb file. I like to call mine [Macro Name] Iteration Data.yxdb.
New: If you have a large dataset, it might work best to start with a strategic subset of your data. A smaller file will speed up macro development runtime. Use a Filter tool before your Output Data tool.
Go ahead and run the outer workflow to write the iteration yxdb.
In my workflow screenshot below, the macro iterates the warehouse inventory. For that reason, I saved the warehouse data to the yxdb at the point right before the macro.
Step 4: Use the new yxdb as your Macro Input.
Existing: Go to your Macro Input for the iteration input. Configure it to a File Input that uses the Iteration Data.yxdb as a template.
New: For a new macro, drag your Iteration Data.yxdb onto the macro canvas. Right-click to convert it to a Macro Input.
Step 5: If you haven’t already, complete the rest of your iterative macro. Be sure to configure the iteration input and output in View > Interface Designer > Properties.
Step 6: Create a user workflow constant for the manual iteration number. More information about workflow constants here and here.
- Click on the canvas, then click “Workflow” in the configuration window.
- Click on the + button to add a new workflow constant
- Populate the information for the user workflow constant:
- I usually call mine “ManualIteration,” which means it’s available in formulas as [User.ManualIteration].
- Start with the value 0. This is where you will update the iteration number when you manually step through each iteration.
- Be sure you check the box in the “#” column. This indicates that the user constant value will be processed as numeric instead of as a string, the default.
Step 7: Anywhere you reference the iteration number within your macro, add the [User.ManualIteration] to the equation as well. Since the value starts as 0, it will not impact the results of your expressions.
Step 8: Create a custom error message that alerts you if [User.ManualIteration] > 0. Since we added this constant to the equations with iteration numbers, forgetting to reset to 0 means we would skip iterations in production.
I usually create a single dummy data row in a Text Input tool and place the following Message tool after it. The Message tool does not reference the dummy data, so the Text Input tool can contain whatever you want.
Step 9: This is where the magic happens! Right next to your iteration Macro Output, create an output that saves to the same iteration input .yxdb as before. Again, I usually call mine [Macro Name] Iteration Data.yxdb. To see which Macro Output is your iteration output, check the View > Interface Designer > Properties window, like in Step 2.
Add the output to a container and disable it.
Step 10: Save your macro.
New: Add the macro to your outer workflow and configure it.
Now, our setup is complete, and we’re ready to use this design pattern for iteration-by-iteration debugging.
Iteration Debugging
Let’s say we need to resolve an issue happening in iteration #5 (remember, [Engine.IterationNumber] is zero-indexed, meaning it starts counting at 0 instead of 1). The iterative macros I build are normally cumulative, meaning iteration #5 depends on the results of iteration #4, which depends on #3, and so on. In order to see the issue in iteration #5, I’ll need to first run iterations 0-4, then iteration 5.
We will store the results of each iteration in that Iteration Data.yxdb.
Step 1: Run your outer workflow to refresh the starting iteration data.
Step 2: Right-click your macro and open it, and make sure the [User.ManualIteration] is set to 0. If you want to investigate any behaviors during this iteration, make sure the ending container is disabled. That will allow you to rerun the same iteration again as needed. Go ahead and run the macro workflow. This is the first iteration, #0.
Step 3: Once you are ready to move to the next iteration, enable the output container and run the workflow again. This should save the output of iteration #0 to the yxdb so it’s ready for the beginning of iteration #1.
Step 4: In your macro, go to Configuration > Workflow to increase the manual iteration constant by +1. Now, we’ll repeat these steps over again. Remember, if you will need to debug iteration #1 at all, be sure to disable the output container again. Run your workflow to see iteration #2.
When you’re ready, enable the output container and click run. Now the output of iteration #1 is saved to the yxdb.
Step 5: Increase the manual iteration constant, disable/enable your output container as needed, and repeat up through the desired iteration.
To reset to iteration #0, simply change your manual iteration constant to 0 and rerun your outer workflow to save a fresh copy of the starting iteration yxdb.
Now you know how to step iteration by iteration through your data!
Eventually, Alteryx might add step-by-step debugging as a feature in Designer, but for now, we have this incredibly helpful design pattern.
Bonus: you can also use this iteration yxdb design pattern to iterate multiple datasets at once through an iterative macro, although this reading and writing might slow down your macro just a bit. Without this hack, iterative macros only support a single iterating dataset. See an example of this from Digan himself during Advent of Code 2022.
Happy solving!
