This is the final part of my blog on Macros in Alteryx and, I will be explaining how to create a Batch Macro. Also, check out my blogs on Standard and Iterative Macros.
What is a Batch Macro?
According to Alteryx documentation: A Batch macro runs multiple times in a workflow and creates an output after each run. The macro runs once for each record (or a selected group of records) in the data. Inputs can be configured to be used in each run of the workflow or only in specific runs. A Control Parameter is the input for each iteration of a Batch Macro. The Control Parameter input appears as an upside-down question mark icon below its input arrow on the macro tool icon. For each record coming into the Control Parameter input, the entire macro is reconfigured and run from beginning to end.
How to create a Batch Macro?
To understand it better, let us create a simple batch macro. Let us say we are working with two datasets, one consisting of order details and the other consisting of the delivery details. Based on the type of delivery selected by the customer the delivery cost needs to be calculated. We can achieve this by using a simple batch macro. The dataset Order is the main input for the macro and the dataset Delivery is the Control Parameter input.
So our input datasets look like below:
As you can observe, in the example Order dataset there are ten records and in the Delivery dataset, there are four delivery types.
Step 1: Creating a running Work Flow
The first important step is to create a workflow that executes the logic correctly. 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, where the Amount is added to the Dellivery_Cost to get the final amount. Then we insert the Browse tool for the output. Our initial workflow will look like below.
In the Formula tool, I created a new field Final Amount which is calculated using the formula [Amount] + 15. The value 15 is the delivery cost for the test run. This value will be later replaced with the input in Control Parameter Tool.
Step 2: Converting the workflow into a macro
Now let’s convert the workflow into a macro. This can be done by converting the input into a Macro input and the outputs into a Macro output. For this, select the Text Input tool, 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 output to macro input and macro output 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”. In the similar way make the necessary changes to the Macro Output.
Let us now add the Control Parameter Tool that will accept the second input. The Control Parameter Tool 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 Configuration area of the Action tool, we need to define the action that needs to be applied to the Formula tool. In the expression ([Amount] + 15) defined earlier in the Formula tool, the delivery cost needs to be replaced with the data set fed through Control Parameter Tool. For this, we need to make the changes as shown below. This ensures that the numeric value in the expression is always updated correctly.
Step 3: Saving the macro and inserting it into another workflow
Let us convert the macro into the right type and save it. From the Workflow tab of the Workflow - Configuration area, we need to select Type as Macro and from the drop-down select Batch 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.
Once you insert the macro, you can notice that the macro takes two inputs, the one with an inverted question mark is for the control parameter.
For the macro to function as expected the mapping between the main input and the input from the Control parameter should be done so that it uses the right field for the calculation. In our example, the delivery cost changes based on the DT_Code and we need to map it with the DT_Code from the main input. This mapping is done by selecting the respective fields in the Control GroupBy Field and in the Input GroupBy Field.
If this mapping is not done, then each record from the main input will be run against every record in the Control parameter input resulting in more records than expected. In our example, it would result in 40 (10 x 4) records.
From the macro output, we can see the macro has functioned as expected thereby calculating the Final Amount by taking the respective delivery amount based on the DT_Code.
That’s it. We have created a simple Batch macro. Hope you enjoyed this reading.
