What is a Batch Macro?
In another blog I cover what a standard macro is: a consolidated process in one tool - in essence a repeated process. A good way to think of it is like knitting a jumper with a single outlined pattern that never changes. A batch macro in this regard is like knitting a jumper for a group where the jumper changes size based on the person. The jumper is essentially the same pattern but is larger or smaller based on who it is for. A batch macro, therefore, is a process that repeats for a group but changes a parameter based on the specific data flowing in.
Why should you use them?
Batch Macros allow you to run similar logic across groups without having to duplicate a process where each process has a minor change in logic. This saves time spent on creating endless flows for each use case, computational costs and time to run a workflow and get the output.
How to create a Batch Macro:
First would be to identify the use case, in the example below we want to find the VAT included with the sales for each record based on the country. As a result we will need to take into account each country's personal VAT rate. Luckily we have the sales data for the company and a list of VAT rates for each country the company has sold in and will bring both in to the canvas:
In order to apply this to each country for each row of sales, this would require either multiple flows branching off the input to conditionally check each country and apply the appropriate calculation for the respective VAT rate or to simply build a batch macro. In a new workflow, drag the input file containing the sales data, right click on it and convert it into a macro input:
Exactly like the standard macro this will change the workflow to a standard macro in the configuration pane which we can leave for now. From here we can now add in a calculation to form a new field of sales + VAT. As the VAT rates are provided in percentages this will be [Sales]*(1+00). The calculation is structured in this format so that the percentage can be applied properly to the total sales amount. Once this formula tool is brought in, the main structure of the macro is completed by bringing in a macro output tool from the interface pane:
Now we have constructed the foundation of the macro we turn our focus to dynamically altering the VAT rate. To achieve this we will bring in a control parameter tool from the interface pane onto the canvas and connect it to the lightning symbol above the formula tool:
Completing this step will automatically add an action tool in between the two and will also change the workflow type into a batch macro, which cannot be changed. The action tool tells the control parameter what it should act upon within the formula tool so we must configure this to work accordingly. First click on the control parameter to give it an appropriate name, in this case "Rate" and then click on the action tool:
You will first find this drop down at the top of the configuration pane to outline an action type, for this example we will leave it as the default of updating value. We must then specify what should update within the formula tool and how:
Here within the dropdowns lists, expression has been selected as we want to specifically alter the numbers going in. This, however, is not specific enough as simply choosing "expression" would likely result in returning each country's subsequent VAT rate. Thus, we click on "Replace a specific string:" and reduce the expression down from [Sales]*(1+00) to just 00. This tells the control parameter that the action should only update the 00 with the corresponding rates which is what we want for our macro to work.
Finally save the macro with an appropriate name "VAT Batch Macro" in this example and return to the original workflow canvas. Right click on the canvas ->input->macros->select VAT batch macro (if the workflow is not open this suggestion won't appear so simply browse for it in your saved documents)
When the batch macro is brought in it will have the following set up and configuration options:
The anchor with "¿" represents the input stream for the control parameter, the bottom left anchor is the input file for the macro to run on. So we will connect the sales file to the bottom and the VAT rates to the top and configure the configuration pane:
The first setting is to choose what field the control parameter will group by, in this case we will select jurisdiction. This works like setting up a join between the 2 input files, therefore this would be the left join field setting, the next setting being the right join:
For this example, as we want to maintain the original granularity of the sales dataset we will select none, if we wanted to have a full inner join country would be selected to match jurisdiction. Finally we click the "Questions" tab and select what field the macro should use in the control parameter:
Here we select rate as that is what we want to parse in to the control parameter and subsequent formula tool. Finally we can run this flow and see the output:
I added a few tools to get the VAT rate in with the relevant fields and we can see the batch macro has worked. This would have taken several different tools and careful logic to do this without a macro and would have been more tedious, time consuming and computationally expensive.
Lastly, if you select view in the very top panel of Alteryx and select "interface designer" or use the Keyboard shortcut CTRL+ALT+D you will see this pop up:
If you click on the settings icon in the left panel, here you will be able to customise your macro icon and also alter how files will be output from the macro. In this example the options are not present, however, in other use cases you will be able to outline how the output should union for each run e.g. do they have the same scheme, auto configure by name/position.
That's everything regarding batch macros and its self explanatory effectiveness and efficiency! As you can see they help automate otherwise arduous tedious tasks with a high probability of making a mistake somewhere which would be difficult to troubleshoot. Batch macros help save tons of time so I hope this blog helps you build your own macros to help your own data processes!