Macro is a very useful part of Alteryx, as it is a custom build tool to perform tasks. There are a few examples already from the Data School cohorts available. (Link: 1, 2, 3)
While trying to study for Alteryx Designer Advanced Certification, I find that I needed to know much more about various Interface tools, a simple macro example is not sufficient, so I would like to share more complicated examples for people want to know about them and my thought on design process.
This series will concentrate on the use of Text to Columns tool.
As the name suggest, the tool is a very useful way of parsing data (without using RegEx), but there are cases where we don't know how many columns do we need to split the original input data. We could guess/try a large number and find the answer or we could split to rows.
This may not be the most efficient way when data set is large or try to find the correct setting to pivot back. So this is a process can be done through a macro to standardize the steps.
Text to Columns requires us to specify which delimiter(s) to be used and for Split to columns, we need to specify how many columns are needed. Those two pieces of information are the ones we would like to control and change.
Note: for the demonstration purpose, we will / as the single delimiter for the setup.
Once we decided which delimiter to use, then we need to figure out how many of them are within the input data, we can use that to deduce how many columns will be needed.
In practice, we are likely to have multiple rows of data, so to make sure we have enough columns for all of them, we will choose the max number of delimiter count with a Summarize tool.
This is the first issue I encountered when building the workflow, as the Summarize input cannot be directly inputted to update Text to Columns tool within the same workflow, this is where the idea of Nested Marco comes in and we will discuss that in Part 2 of the series.
Note: We will continue by assuming 3 columns (default) to split.
So let's first deal with change of delimiter in the macro. To allow users some customization in using the tool, we can attach a Text Box from Interface tool to allow the user to choose which delimiter they want to use.
Text box is a very simple tool to configure, we can leave the default setting (just changing the interface text). All the interface tools has a Q(uestion) icon at the bottom of the tool and we connect them to the lighting(action) icon, an Action tool will always be connected in between automatically.
Note: Sometimes it is possible to connect directly from Q to Q between two tools, but it is recommended to use Action tool for detailed configuration and debug.
For both Formula and Text to Columns tool, we would like to update the / in the initial tool that we had inputted when setting up the workflow. Remember to select the last option to update the value.
We can save the macro and test it. All the workflow are packaged into a single icon.
Let's test with an example input
We can see the macro can calculate three / exists in input data, where the result columns are in 1, 2, 3. Notice the last column also has / in it, as the default Text to Columns tools is 3, so it did not split it despite the existence of delimiter.
In the next part of the series, we will see how we can incorporate the calculated delimiter count into the macro to improve it.
Looking for more guides, tips and tricks in Tableau or Alteryx? Go check out the other blog posts from the Data School.
