This is the second part of my blog on Macros in Alteryx. In this blog, I will be explaining how to create an Iterative Macro. Check this to know about types of macros and how to create a Standard Macro and this to know about Batch Macro.
What is an Iterative Macro?
As the name suggests in an iterative macro, the workflow iterates a certain number of times which is defined through a condition. It is like using a while statement in the programming language.
How to create an Iterative Macro?
To understand it better, let us create a simple iterative macro. I will explain using the same example used during our training. It would calculate the number of years required to reach a target amount for the provided investment amount. It would also enable the user to
a. select the interest rate and
b. provide a target amount
Step 1: Creating a running Work Flow
We start by creating a workflow that executes the logic correctly, which is very important. We later convert this workflow into a macro. To create the initial workflow, we insert a test input in the canvas using the Text Input tool. Then we will insert a Formula tool, which is where the investment amount is multiplied by the interest rate. Followed by we will insert a Filter tool where we include the condition for the workflow to iterate. And finally, two Browse tools one each for the True and the False output. Our initial workflow will looks like below.
The test input I created using the Text Input tool is as shown below.
In the Formula tool, I entered the formula [Investment Amount] + ([Investment Amount] * 0.04). The value 0.04 is the interest of 4%used for the test run. This value will be later replaced with the user input.
In the FilterTool, I provided the condition that needs to be checked as shown below. The workflow iterates until this condition is false.
Step 2: Converting the workflow into a macro
Now let’s convert the workflow into a macro. This can done by converting the input into a Macro input and the outputs into a Macro output. For this, select the Text Inputtool, right-mouse click and select Convert to Macro Input. In the same way, convert the Browse tool into Macro Output.
After converting the input and outputs to macro input and macro outputs respectively, we can provide the corresponding anchor abbreviation for each of the tools to make it clear for users. Select the Macro Input tool and change the Input Name to “Input” and Anchor Abbreviation in the Configuration area to “i”.
Since there are two outputs, they also need to be named properly one as Iterative outputand the other and Final output. The Anchor Abbreviation also needs to be named accordingly.
As per our macro requirement, an option needs to be provided to the user, to select the interest rate. To achieve this we will insert a Drop Down tool from the Interfacepane. The Drop Down is then connected to the Formula tool. In doing so an Action tool is automatically inserted into the canvas. In the Actiontool, we can define the action that needs to be applied on the Formulatool.
In the Configurationarea of the Drop Down tool, update the text field with the relevant text that needs to be displayed. From the List Values, select Manually set values and in the Properties area enter the values which we want to display in the drop-down and the actual value that needs to be used in the Formulatool separated by a colon as shown below. For example, 3% will be displayed in the drop-down and the corresponding value of 0.03 will be used in the Formulatool when the user selects 3%.
In the Configuration area of the Action tool, we need to define the action that needs to be applied to the Formula tool. In the expression ([Investment Amount] + ([Investment Amount] * 0.04)) defined earlier in the Formula tool, the interest rate needs to be replaced with the user-provided value. For this, we need to make the changes as shown below. This ensures that the numeric value in the expression is always updated with the user-provided value.
Now let us add the second option for the user input where the user can enter the target amount that needs to be achieved For this, we use the Numeric Up Down tool. This is connected to the Filter tool. Just like in the previous step, an Action tool is automatically inserted in the canvas when Numeric Up Down and Filter tools are connected.
In the Configurationarea of the Numeric Up Down tool, we enter the text that needs to be displayed and update properties of the value as shown below.
In the Configuration area of the Action tool, we need to define the action that needs to be applied to the Filter tool. In the condition defined earlier in the Filter tool, the numeric value needs to be replaced with the user-provided value. For this, we need to make the changes as shown below. This ensures that the numeric value in the expression is always updated with the user-provided value.
Since we want to find out in how many years the target amount for the selected interest rate will be achieved, we need to create a new field No of Year(s). This can be done in the already inserted Formula Tool. We can use the existing variable Engine.IterationNumber which counts the number of iteration for each loop. Since it starts with 0 we need to add 1 to get the actual number of years. (To enable the variable Engine.IterationNumber the macro should be changed to Iterative. Refer to Step 3 for this)
Step 3: Saving the macro and inserting it into another workflow
Before saving we need to change the type of the workflow to macro. From the Workflow tab of the Workflow - Configuration area, we need to select Type as Macro and from the drop-down select Iterative Macro as shown below. Now let us save our macro. The macro workflow will be saved with the .yxmc extension.
The saved macro can now be used in another workflow. Open the workflow where you want to insert the macro. On the canvas area, right-mouse click and select Insert and then Macro. From the Open File window, browse the location where you saved your macro and select it to insert it in the canvas. Connect the macro input and output of the macro with the respective tool.
With that you learned how to create an Iterative macro. I hope you enjoyed reading this blog.