In Python, while loops can hold two separate functions. They can iterate until you've reached a certain number of iterations (while i<5: function() i+=1), or iterate until a True value is reached (while x<5: function()). In Alteryx, you can still do both, in a manner that is simple to understand. It is called an Iterative Macro.
In order to build one, we need some data. I am going to use two investment amounts.
We want to use loops to find how many years, until we can afford a new car.
In Python you would write
while df[Investment]<5000:
df[Investment]*(1+(2/100))
where 2 is the growth rate of 2%.
In Alteryx we can build an iterative macro
- First we have to build a sample data set. We can drag in a text input tool from the In/Out pane, and type a sample value directly into the table. Within the text input tools data, we need to include a column for year (which should start at zero) and a column for growth rate (which you can decide, but I set it to 2).
- Next, right click the icon and press 'convert to macro input', this will turn our canvas into a macro (.yxmc file).
- Now we have to click into the white space, on the canvas, then go into the configuration tab on the left hand side and press workflow. Under that, it should say, 'Type: .... Macro', which should already be selected. We want to turn the type of the macro, from a standard macro, to an iterative macro, using the drop down menu, to its right.
- Now we should build our workflow we want to iterate (think of this as building a function in Python).
The investment formula will increase the investment column by 2% (our growth rate) each time the formula runs, and the year column will increase the number of years by 1.
- Next we need to give our condition for our while loop. We do this using the filter tool.
- And now, let us add two macro outputs to the T and F anchors. You may be wondering, why two? Well, we will now set the F anchors output to iterate, and the T anchors output to give the final answer.
Open your 'Interface Designer', if you cannot find it, go to 'View' in the top left panel and make sure the word 'Interface Designer' has a tick on the left hand side. If not, click it.
Once you are on your interface designer, on the left hand side should be a series of icons. Press the cog.
You should see this. Here you can input your 'Iteration Input' (which input you want to iterate on), and your 'Iteration Output' (which 'macro output' you want to carry on iterating). The way we set up our filter, means that the want the F anchor to be our 'Iteration Output', as we want this tool, to send us back to the start, to iterate once more.
- Here, you can input your 'for loop'. You can choose how many times you want it to iterate using the 'Maximum Number of Iterations' box. But, it will only reach that level if your filter allows it.
- Now you have set up your macro. Make sure to save it somewhere you can find it again and now we can input our macro to a workbook.
- You can do this by right clicking the canvas on a new workbook, and press Insert, Macro, and choosing the macro. Attach the data set above and have fun!
Another interesting tool you can use it Alteryx with macros, it provide the ability for the user to input data which would be used in the formula. For example, you could input the growth rate or starting year. I will go through this in my next blog, so stay tuned!