Have you ever needed a calculation to repeat until you reach a certain condition? For example, the interest of a loan being calculated until the loan is paid off. Then you can use an iterative macro to make your life so much easier.
An iterative macro is a tool that runs a sequence of Alteryx tools and repeats the sequence until it reaches a certain output.
To start with the macro, we first need to define the format of the data that we are inputting into it. To do this, we can right-click the input tool and select "Convert to Macro Input."
Now it's time to build the workflow.
I will create a macro for the example I mentioned earlier, concerning the interest rate of a loan. So, let's say the data I have is the total amount of the loan, and I need to calculate how long it will take to pay off the loan, based on a fixed interest rate and fixed payments on the borrower's part.
To start, I will add a formula tool that subtracts the borrower's payment for each month, one that adds the interest to the loan, and one that tracks how many months have passed.
The first equation subtracts the borrower's payment from the loan amount of 7140 (Field1 in the bottom right). I am setting the borrower's monthly payment to 175 for each month. The second equation calculates the interest rate, with the default interest rate set to 4.99%. The last equation tracks the iteration number.
After making the calculations, we need to set the condition for when the macro stops repeating. To do this, we can use a filter tool to set the condition. For my macro, the condition I want to set is that the loan amount is equal to or less than 0.
Now, on both ends of that filter tool, place a macro output tool, and you've built the workflow for your macro. If you want to make it more dynamic, you may add interface tools to the macro so people can adjust variables in the flow, such as the interest rate and payment amount.
The last thing we need to do before using the macro is to configure the outputs. To do this, we need to open the interface designer. Go to the "View" tab at the top of your screen and click "Interface Designer." In the interface designer, go to the properties tab on the left (the gear icon).
In the properties tab, we need to set the iteration input, the iteration output, and the max number of iterations. The iteration input is the input of the macro, the output is the output of the macro that you want to loop back to the start of the macro, and the max number of iterations is the number of times the macro will run if not all of the rows meet the condition.
With that, plug your macro into the data set and press run. Now, you have made an iterative macro.