community
cancel
Showing results for 
Search instead for 
Did you mean: 

Engine Works Blog

Under the hood of Alteryx: tips, tricks and how-to's.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
Alteryx
Alteryx

Sub Grouping Macro

 

 

 

MacroImageNew.png

 

 

 

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:

 

  1. Go to Options in the menu bar
  2. Go to User Settings
  3. Go to Edit User Settings
  4. 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. 

 

 

XML_ConfigWindowNEW.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Below is the XML I built to pass into the Summarize tool.

 


XML_GroupingFields.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

XML_SummarizeFields.png

 


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).

 


SummarizeToolBeforeBatchMacro.png

 

 

ConcatenateXML.png



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.


Output.png

 
MacroOutput.png

 

 

 


ReportOutput.png

 

 

DataOutput.png

 

 

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.



ErrorMessage.png

 

 

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.

 

 

WarningMessage.png

 


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.

Denise Frey
Content Engineer

Denise began using Alteryx as a customer prior to joining Alteryx as a Content Engineer in May 2016. She really enjoys using her creative side to develop new tools to help customers solve problems more efficiently. When Denise isn’t working, she enjoys running, skiing, and camping in beautiful Colorado.

Denise began using Alteryx as a customer prior to joining Alteryx as a Content Engineer in May 2016. She really enjoys using her creative side to develop new tools to help customers solve problems more efficiently. When Denise isn’t working, she enjoys running, skiing, and camping in beautiful Colorado.

Labels