Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Since 1.0, people have been requesting looping in Alteryx. We have added a lot of features over the years to give various types of loops. The GenerateRows & MultiRowFormula tools gave looping functionality within a module, but were not sufficient for complicated algorithms. In 5.0 BatchMacros gave a very robust looping function – in effect the equivalent of a foreach style loop. While solving many problems, it did not (at least not easily) work for iterative algorithms. What is an iterative algorithm? From Wikipedia:
In computational mathematics, an iterative method attempts to solve a problem (for example, finding the root of an equation or system of equations) by finding successive approximations to the solution starting from an initial guess.
The issue (in the past) with Alteryx is that all the looping methods were deterministic. There were various ways to run a fixed number of iterations over a dataset, but no way to run a variable number of iterations. In 6.0, we introduce iterative macros.
An iterative macro has a special output that if it contains any records will cause the entire macro to run again with the left over records as an input. It will run as many times as needed until there are no more intermediate records or a predetermined record limit is reached.
To demonstrate, I created a module that calculates a square root with the Babylonian Method. Most iterative algorithms will look very similar.
Step 1: Make sure you have a result field (or fields). This can be a little tricky for the 1st iteration. In this case, I add a field with a union tool, making sure not to add any records.
Step 2: Do the calculation for this single iteration. Note the second formula that uses the special engine constant: Engine.IterationNumber. This allows me to output how many iterations each value took to converge on the correct answer.
Step 3: Decide which records records are done and which need more iterations.
My guess is that most iterative algorithms will fit in this same pattern.
Note: This module will require at Alteryx 6.0.2.21305 or better to run. It can be found here.
