01-09-2018 01:02 PM - edited 08-03-2021 11:29 AM
This article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Macro Input Tool on our way to mastering the Alteryx Designer:
When using a macro placed in a workflow, the Macro Input Toolwill be used to pass data from the workflow to the macro. Becoming a master of the macro input will allow you the flexibility to map your fields, have optional inputs, and even optional fields.
I’ll be using some birth rate datafrom the World Bank Group. I’ve included the relevant data in the attachedv11.7 Alteryx workflow Macro IN.yxzp. Here is a quick look at the original data in Alteryx:
Notice that the data spans 1960-2015. Let’s suppose that we wanted to sortthis data based on a single year that the user will select when running this from their designer. Here is what the macro and macro input configuration looks like:
Let’s start with the macro configuration. We’ve selected the Text Input's radio button and entered some sample data as shown on the bottom left. Note that you could also setup a Text Input Tooland then right click on it to convert it to a macro input. Alternatively, we could have pointed to a file/db and used that data as our sample data. This data is used to setup and test our macro, but it is completely ignored when we use the macro from a workflow that supplies its own input data (although the column names may get used as shown later). Alteryx provided a default Input Name and blank anchor abbreviation when we dropped it on the canvas. We unchecked the Show Field Map (it’s checked by default). We’ll provide a use case below where showing the field map should be checked. We’ve left the optional incoming connection unchecked as this input data will be required.
As for the macro itself, you’ll notice that our macro input is using both of its outgoing connections. The normal tool anchor is passing the data downstream to the SelectTool. The Macro output anchor is connected to the Drop Down Tool. This is a neat feature where the drop down list will be populated based upon the data connected to the macro when it’s ultimately placed in a workflow. Here is what it looks like from the workflow:
Since we’re not using the show field map option, our field names are not changed by the macro input tool. We connect the drop down to the ActionToolsto help with column header for the field we select. If you look at the output, you’ll see that only the field we select is coming through. You’ll notice that the macro input has been configured with sample data like “Name”, “Company”, “Indicator_Code” etc. Our actual data set in our workflow doesn’t contain fields with these same names. Because the select tools in our macro are deselecting any unknown fields, these fields are falling out. Our next use case will fix this issue.
A key feature of the macro input is the ability to map fields. Here is the second macro (macro1):
The only change to the macro input configuration is that we’ve checked the show field map button in the macro configuration. When added to our workflow, it’s now having us map the fields from the workflow to the fields in the template input of our macro input tool.
Note that this is where the user can select whatever year they want. It’s important to note that the macro input tool will now change the names of the incoming fields to the match the template data. So our field “2015” will be renamed “Year” to match the header in our macro input tool. This allows us to delete the drop down and related action tools. The only thing we add is an action tool from our macro input to a select tool to change our field headers back (so that “Year” is returned as “2015” in our example.) The action tool accomplishes this through the “Update Select with Reverse Field Map” option. Note that in some cases you may want the field names to be changed/standardized and you wouldn’t need the action tool in that case.
Note that all fields must be mapped in this macro. I’ve eliminated a couple fields and now the macro has an error when I leave fields unmapped:
Our next use case will demonstrate how to make fields optional.
There is a neat little feature in the macro input tool that allows for mapped fields to be optional. We’ll update our template data by adding “ (Optional)” at the end of our field names.
This one simple change now allows us to optionally map these fields. If we don’t map some of them, it will run without errors.
Let’s suppose that we want our macro to be able to append a unique country code that our organization uses. Our macro needs to be able to smart enough to run without error whether or not this extra data is actually supplied. Here is how we would add an optional macro input:
Our new macro input on the right has the field map and optional incoming connection checked. By making this input optional, the macro doesn’t require that anything be connected to it in the workflow. It signifies that it’s optional by changing the color from green to grey:
Our workflow runs without error whether or not data is connected to the optional input. Do note that 0 records with the template headers are passed in the macro if nothing is connected in the workflow.
The macro input tool is key to understand whenever you are working with macros. It has powerful options like mapping fields, optional fields, and even optional connections. It can connect to regular tools or interface tools through its 2 outputs. For more info on macros, check out the CS Macro Dev series!
By now, you should have expert-level proficiency with the Macro Input Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.
Stay tuned with our latest posts everyTool Tuesdayby followingAlteryxon Twitter! If you want to master all the Designer tools, considersubscribingfor email notifications.
Hi,
Thanks for the post, its truly helpful. I have a quick question. Can I create a macro that iterate by columns? For example, first iteration will do the operation with the first column, second with the second column, etc. Let me know your decision. Have a awesome day.
@FredySantana You should be able to. I don't think it's very straightforward though. Your best bet would be to post something to the designer forums with a simple example of what you're looking for.
thank you @patrick_digan - this is super comprehensive for beginners and a great top-up for folk who have a bit more experience.
(i didn't know about the reverse field map action tool option until reading this - that is going to be helpful)
thanks really helpful, the (Optional) fields are a really neat feature
I wonder if the remove null column standard macro is better than the data cleansing tool from a performance point of view?
Can someone help me out with the Map field part. Do we need to make any changes in the macro which is related to " I’ve eliminated a couple fields and now the macro has an error when I leave fields unmapped" part.
If yes, then please help me out and let me know how this needs to be done