An Alteryx Macro is a combination of many tools of a workflow built into a single tool that can be inserted into another workflow. The user can save the macro and re-use it without re-create the workflow. Macro helps to solve the problem faster if the workflow repeats multiple times. There are 3 different kinds of macros in Alteryx: Standard macro, Batch macro, and Iterative macro.
In this blog, I will go through each type of macro by introducing what is that macro and discussing the advantages of each macro, and when to use it.
Are you ready to explore Alteryx macros? Let's get started now!
1/ Standard Macro
During cleaning and exploring the data, you apply the same process with the same group of tools multiple times. When the workflow is repeated many times, you can consider using standard macro in Alteryx. It will help you save time when re-use the workflow.
For example: I have 2 tables in Alteryx. 1 Table has ID and Category columns and another table has ID and Product columns. I want to get the code, year, and ID from the ID column. (Image 1)
The structure of data in the ID column of both tables is the same, so the workflow to prepare the data is the same. In this case, I use the standard macro. I copy either one of 2 workflows into a new worksheet (New Workflow). Then, convert the Input tool and Browse tool to Macro Input and Macro Output (Image 3).
After converting the Input and Browse tools to Macro Input/ Macro Output, click on any blank space on the canvas. In the Configuration window, click on the Workflow menu, and you will see Alteryx automatically choose Macro and select Standard Macro (Image 4). Then save the Macro; Alteryx recognizes that it is a macro, so the file type is .yxmc.
Going back to the original workflow, I deleted all tools between the Input and Browse tools. Then, right-click on the canvas, I choose Insert > Macro > new_macro (the macro file name that I saved in the previous step) (Image 5).
Then, I connect the macro with the Input and the Browse tool. Finally, run the workflow. I got the result I wanted (Image 6).
The standard macro is useful when you need to use the same process for many datasets or many sheets in Excel with the same structure/ format.
However, if you have an Excel file with many sheets, each sheet has the same structure/ format but you don't want to show all the sheets, Browse tools with the same macro on the canvas, Batch Macro will be helpful in this case.
2/ Batch Macro
When a process is repeated multiple times and for each time you want to specify a different value and change the output, you can consider using Batch Macro. I usually apply the batch macro when there are many sheets in an Excel file or I do web scraping with multiple pages and want to get all data for all sheets or pages.
For example, I have an Excel file that includes 3 sheets: Q1, Q2, and Q3 (Image 7). Note that in the Q2 sheet, the last column name is Revenue, which is not similar to other sheets.
My goal: Combine all worksheets in the Excel file into 1 single output.
- Building a Batch Macro
First of all, I see that the directory for the file is the same (because one Excel file). In the Excel file, there are multiple worksheets (Q1, Q2, Q3), so I only need to change the name of the worksheet for the input.
As I mentioned the problem above, in Q2, the column Revenue is not the same as other columns in another sheet. I need to rename it in the workflow. I used the Dynamic Rename tool to check if the [_CurrentField_] (column name) is 'Revenue', then I will rename it to 'Sales'; otherwise, I will keep the column name (Image 9).
After that, I need a tool to change the worksheet name when I switch the worksheet to get the data. I use the Control Parameter tool in the Interface palette (Image 10).
1/ I drag the Control Parameter from the Interface palette to the canvas.
2/ Connect the Q port from the Control Parameter to the thunderbolt of the Input Data tool.
3/ After you make a connection, Alteryx automatically creates an Action tool between the Control Parameter and the Input Data tool. Click on the Action tool to open the Configuration window on the left.
4/ In the Configuration window, select the directory path (Start with File - value). That is the directory of the file. Note that at the end of the directory is the sheet name (Q1).
5/ Now, I want to change only the sheet name. Check the box Replace a specific string. In the box, I type Q1. It means I only want to replace the Q1 string with another string and keep the rest text.
Finally, connect the Browse tool to the Dynamic Rename tool. Then right-click on that Browse tool and choose Convert to a Macro Output.
If you click on the blank space on Canvas, then select the Workflow menu. Alteryx automatically recognizes this is a Batch Macro (Image 11).
Another action before saving the macro is checking the Interface Designer. I go to View Menu on the top and choose Interface Designer (or hold Ctrl+Alt+D at the same time) to open the Interface Designer window (Image 12).
In the Interface Designer window, I choose the Properties icon. Then select Auto Configure by Name (Wait Until All Iterations Run). It means when all Iterations run, at the end it will put fields with the same name in the same field.
Then, save the macro with the data type as .yxmc. I saved it as batch_macro_test.yxmc.
Now, I open a new workflow and drag the Excel file to this new workflow. However, at this time, I choose Import only the list of sheet names. Then, run the workflow; it will show only the sheet names (Q1, Q2, Q3). (Image 13)
In the next step, I insert the batch_macro_test that I just saved into the workflow (Image 14).
After connecting the Input Data tool to the upside-down question mark anchor (representing the Control Parameter). Select the batch macro and choose the field Sheet Names (V_WString) (Q1, Q2, Q3). When we run the workflow, it will plug each sheet name into the Control Parameter in the batch macro. Finally, add the Browse tool to the batch macro and run the workflow (Image 15).
Now, all the data from all 3 sheets is in the output result. As you noticed, there is no Revenue column in the output. Only exist the Sales column (Image 16).
That's the batch macro in Alteryx. If I don't use the batch macro, I have to import the Excel file 3 times for each worksheet. Then, I also use the same workflow for each data input. Imagine that you have multiple Excel files and each Excel file has multiple sheets, how can you handle that? Batch macro is a good choice in that case.
In this blog, I introduced what is macro in Alteryx, and how many types of Macro and went through a demo for each type of Macro in Alteryx. This blog is long, so I will write the Iterative Macro in the next part. I hope from this blog, you understand the difference between the Standard Macro and the Batch Macro; also, you know which cases need to use what type of macro. Have fun!
Hope to see you soon in the next blog!