Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Sub Grouping Macro
The idea for this macro stemmed from the following community post: http://community.alteryx.com/t5/Data-Preparation-Blending/Sub-grouping-solution-needed/m-p/21012/hig...
The thought behind it is that there are times that you would like to create sub grouping levels in your data. In order to do this without a macro, you would need to add multiple Summarize tools along with some Sort tools and Select tools. If you have more than one report, this could become tedious and time consuming. With a macro, it does this for you and you only need to drop in the macro tool right after your Input Data tool. It allows a you to select two levels of grouping, and it can group on any field type. In addition, you will be able to select which fields to perform an operation on and what type of summation method to use. The default method is Sum.
I needed to figure out if I was going to limit the fields on which to perform an operation or allow the user to choose as many as they would like. I elected to go with the latter. I also decided I was going to let the user select the summation method from a list of choices.
At this point, I needed to figure out how to make this happen. I knew I needed to use the interface tools, but how? These were tools I had never used before, but I love a challenge!
My first pass at attempting this included updating a specific number of values in the Summarize tool. I quickly realized this was not going to work because I would not be able to dynamically select the number of fields in which I would like to assign a summation method. When doing it this way, you are limited to only the number of Drop Down tools you use in the macro, and I did not want to limit the user.
I started researching and found this article posted by Andrew: http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Update-Allocate-Append-tool-using-XML/ta-p/48...
Updating raw XML? Another area I had never entered; however, I knew this was going to be the only way for me to allow the user to dynamically select fields they would like to perform an operation on.
In order to view the XML in the Configuration window, you will need to do the following:
- Go to Options in the menu bar
- Go to User Settings
- Go to Edit User Settings
- Click on the Advanced tab. Check the box that says “Display XML in Properties Window”.
Now you will see the selection for XML in the Configuration Window. The reason I needed to see this view was to get a template for the Summarize tool so that I knew what I needed to build before I built it.
Below is the XML I built to pass into the Summarize tool.
In order to pass XML through, I needed to create a batch macro to update the configuration of the tool. I couldn't pass XML into the Summarize tool without creating this. If there was a way to take the XML from the input to the connection in the tool, we wouldn't need the batch macro.
The data is concatenated in the Summarize tool, prior to passing it through the macro using a Union tool and Formula tool. The Formula tool is looking to see if any of the users' selections for the fields to summarize are True. If any of them are, it will concatenate the Top Level Grouping and 2nd Level Grouping fields along with the fields to perform the operation on, as well as what operation to perform (ie: Sum, Avg, Count, etc).
Next, I needed to figure out what the output should look like. There are a couple scenarios for how a user might want to view this data. One is a simple data file that they can then use for further analysis (Data Output). Another is formatted for a report (Report Output). I added two Macro Output tools in order to accommodate both scenarios.
The last step, was to add error messages. Doing this creates a more user friendly macro experience. I added two messages.
The first, using the Error Message tool, checks to see if the user has selected the same field for both Top Level Grouping and 2nd Level Grouping. In doing this, the output would create null values. In order to keep this from happening I am alerting the user to make this change prior to running the macro.
The second, using the Message tool, lets the user know they have selected a “String” data type along with “Sum” or “Avg” for the operation method. If they choose these, the warning lets them know it will create NULL values.
Well, that is all I have for today. I hope you enjoyed reading this post! Feel free to provide any feedback or questions in the comments section!
I have included an example below, however, the Sub Grouping Macro can be downloaded from the gallery here.
