As defined by Alteryx:
A macro is a workflow or group of tools combined into a single tool that you can insert into another workflow. Create a macro to save and automate an analytic process you perform repeatedly. Use the macro within a workflow without the need to recreate the analytic process each time.
A number of tools already in Alteryx are actually macros. For example, you can see this when right clicking on the Data Cleansing tool.
Now there are three types of macros:
Standard Macro
Used for shared start to end process
Batch Macro
Used to run a process for each individual record
Iterative Macro
Used to run a process until a condition is met
Although all of these are useful to know, the iterative macro has a lot of use cases, especially in the finance industry. Below we will go through how to make a simple iterative macro to calculate how long it would take to reach a target goal given a starting value and an interest rate.
How many years would it take to afford a house in London?
Let's start by using the average savings in London of £28,978 to calculate how long it would take to afford the average house price of £673,034 with an annual interest of lets say around 4.5%.
Firstly, we are going to enter with our savings, interest and target values with a Macro Input tool (feel free to name this). In this tool we have the option to enter our values as a text or using a file input. For now we will just use the text option.
At this point, it might be worth adding a Select tool to double check the data types for your fields are numeric. As the numbers will get quite big I would change the data types of your numeric fields to Int64.
As we are counting the amount of years it will take to reach the target value we need to add a Formula tool that will act as counter for how many times the macro must loop for the savings to reach the target.
The above formula uses a variable called [Engine.IterationNumber] which counts the number of iterations the macro has undergone. This count starts from zero so a one is added to get the correct number of iterations or loops.
Another Formula tool is needed to calculate how much savings have increased after the interest rate is applied to it.
Note that the interest values are percentage values so dividing them by 100 is required to create the multiplier. We are also going to be outputting to the savings field so as to rewrite the savings value in preparation for the next iteration.
Now we need to add a condition to test if the macros is to continue iterating. In most cases, including this one, it will be a Filter tool. The logical test we are using is whether savings is above target.
From the previous filter tool we are going to connect two Macro Output tools, one to the T (True) branch and the other to the F (False) branch. We will call the macro output tool coming off the T branch as the Final Output and the macro output tool coming off the F branch as the Iterative Output.
The workflow itself is complete but the macro still needs to be setup to enable iteration. To do so, click on the canvas on select the workflow tab in the configuration panel and make sure in the macro dropdown menu iterative macro is selected.
Next, click the view tab in the top left and select interface designer (or press Ctrl + Alt + D). Once in the interface designer window, click the properties icon (little cog) and make sure your iteration input and iterative outputs are selected.
Customise the options as you see fit but I would recommend at least having the warn option selected so you know at what point your macro iteration stops. Once this macro has been created we can now place it in any other workflow using different inputs (of the same format). Below is the finished macro.
And if we run this macro in a separate workflow we can see that it would take about 72 years to reach our target goal.
