Hello! Let's dive into our next topic in Alteryx macros.
In our last lesson, we mastered using the Action tool in a standard macro. We learned how to connect an interface tool (like a Drop Down) to a workflow tool, allowing a user to update its configuration once before the macro runs.
Today, we're taking that concept to the next level with batch macros. While a standard macro runs once, a batch macro is designed for repetition. It runs its entire workflow over and over, once for each record in a special "control" input data stream. This is incredibly powerful for automating repetitive tasks.
By the end of this 60-minute lesson, you will be able to configure a batch macro using a Control Parameter to process records sequentially. This is a core skill for the Alteryx Advanced Certification.
1. What Makes a Macro a "Batch" Macro?
The key component that defines a batch macro is the Control Parameter. Think of it as the driver of the macro.
- In a standard macro, an interface tool like a Text Box captures a single value from the user to configure the workflow.
- In a batch macro, the Control Parameter receives a stream of records (a column from your data). For each record in that stream, the macro runs from start to finish.
Let's start by reading a formal definition.
Macros in Alteryx – Batch Macro
The article 'Macros in Alteryx – Batch Macro' from The Data School provides a concise definition and introduces the Control Parameter.
Please read the section titled 'What is a Batch Macro?'. Pay close attention to the description of the Control Parameter and its unique upside-down question mark icon.
As the article states, for each record that enters the Control Parameter, the entire macro is reconfigured and run. The results from every run are then unioned together at the end to create a single output.
2. Building a Batch Macro: Step-by-Step
The best way to understand this is to build one. We'll walk through a practical example of calculating the number of boxes required for different products, where each product has a unique box size.
The video 'Alteryx Batch Macros' from The Information Lab provides a perfect demonstration. First, let's watch the introduction to understand the problem we're going to solve.
Watch from the beginning until 01:26. This will set up the business problem and show you the expected input and output.
Now that you understand the goal, let's build the macro. The process is:
- Build the core logic in a workflow.
- Introduce a Control Parameter to provide the changing value (the box size).
- Use an Action tool to tell the macro what to update with that value.
Let's continue with the video to see how the macro itself is constructed.
Watch from 01:26 to 04:51. Pay close attention to these key moments: A placeholder value (6) is used in the Formula tool. Dragging the Control Parameter onto the canvas automatically changes the workflow type to a Batch Macro. Connecting the Control Parameter to the Formula tool automatically adds an Action tool. The Action tool is configured to replace the specific string 6—the same skill we learned in the last lesson!
To summarize the video's steps for building the macro:
- Macro Input: Contains the test data that will be processed (the
Unitscolumn). - Formula Tool: Contains the core logic. A placeholder is used for the value that will change with each batch run:
CEIL([Units] / 6). - Control Parameter: This is the entry point for the list of box sizes that will drive the batches.
- Action Tool: This connects the Control Parameter to the Formula tool. Its configuration tells Alteryx to find the
6in the formula and replace it with the value coming from the Control Parameter for that specific batch. - Macro Output: Gathers the results from each run.
3. Using and Configuring the Batch Macro
A batch macro doesn't run by itself; you use it within a standard workflow. This is where you feed it the main data and the control data. This step involves a critical configuration that can be tricky if you're not aware of it.
Let's watch the final part of the video to see how to use our saved macro and, most importantly, how to configure it correctly.
Watch from 04:51 to the end (09:08). Focus on: The two different input anchors on the macro tool: the standard input and the control input (upside-down question mark). The initial, incorrect result of 27 records, which happens when the macro isn't configured properly. The crucial use of the Group By option to match the control records (box sizes) to the main data records (units per product).
The Importance of "Group By"
This is the most critical part of configuring a batch macro.
-
Without Group By: The macro performs a Cartesian join-like operation. It runs for every record in your main input against every single record in your control input. In the video's example, this meant every product was calculated against all three box sizes, leading to
9 data records * 3 control records = 27 output records. -
With Group By: You are telling Alteryx to match the records from the control input to the records in the main data input based on a key field, similar to a Join tool. In the example, by grouping on
Product, the macro knew to only use the "Box Size = 6" batch for "Product A" data, "Box Size = 12" for "Product B" data, and so on. This resulted in the correct 9 records.
This concept is so important that it's worth reading a quick summary about it.
Macros in Alteryx – Batch Macro
Let's revisit our first article, which gives a stark warning about what happens if you don't configure the Group By (or 'mapping') correctly.
Read the two paragraphs directly under the image of the macro with the two inputs. The text explains that mapping is done via 'Control GroupBy Field' and 'Input GroupBy Field' and warns that without it, 'each record from the main input will be run against every record in the Control parameter input'.
Conclusion
You have now built and configured your first batch macro! This is a significant step forward. You've combined your prior knowledge of the Action tool with the new concept of the Control Parameter to create a powerful, automated workflow.
Key Takeaways:
- Batch macros repeat a workflow for each record provided to a Control Parameter.
- The Control Parameter input is identified by an upside-down question mark anchor on the macro tool.
- You still use an Action tool to specify which part of a tool's configuration should be updated by the control value for each batch.
- The Group By configuration on the macro is essential. It matches control records to data records, ensuring the correct data is processed in each batch and preventing an undesired cross-multiplication of your data.
In our next lesson, we will apply this powerful new skill to a very common advanced use case: building a batch macro to process all files in a directory that share the same structure.