Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
* In a “2-degrees of separation between ACEs who write articles about Kevin Bacon” twist of irony, please note that this Alteryx + Kevin Bacon article is different than the other Alteryx + Kevin Bacon article about the Make Groups tool. While there may be some potentially similar applications, using the Hierarchy Macro vs. the Make Groups tool will provide very different end results. As with all movies where Kevin Bacon is the star, please choose carefully.
I’ve discovered over time that there are occasionally situations when I find myself thinking, “Hey! That random thing I learned back in elementary school actually did come in handy!!” This has yet to occur for quadratic equations, dodgeball, or using the phrase “I ride to school on my moped” in French…BUT I was pleasantly surprised to discover that gluing pictures of my family members onto a construction paper tree did eventually come around full circle.
I’m talking about hierarchies. In every department, in every industry, there will always be scenarios where you need to find how something “rolls up” to the next or highest level. This could be product hierarchies, employee-manager HR diagrams, or a roll-up that shows how GL Account 823188138 rolls up to the Short-Term Lease Liabilities line item on a Financial Statement.
The Background Info
Now generally speaking, the concept of a hierarchy is straightforward – this thing rolls up to that thing, and that thing rolls up to the top thing. For example:
What is not always so straightforward is deriving which node something rolls up to when it starts out as a long list of individual parent-child relationships, or you are tasked with determining all the base level values that might be included in a particular hierarchy node.
Enter Alteryx, the Solver of Repetitive Things!
When faced with a basic hierarchy problem, you might start out thinking “I just need to join my Parents back to the list of Parent-Child relationships to determine each Parent’s Parent (i.e., dear old Gran), and then I’ll have my complete hierarchical list!” This works particularly well if you only have, say, one or two levels of Children and Parents. Add a couple Join tools, Union your results, and voila – you have some hierarchical data that shows you how your Children relate to their Parents (How they culturally relate? Not at all, but how they relate via the data? Quite logically.)
However, if you’re looking at a hierarchy with more than one or two levels, this will rapidly become a process that’s unnecessarily repetitive, even in Alteryx – Join after Join after Join after Join, all Unioned back together with a lot of obnoxious column renaming (since I’m assuming that dear old great-great-Gran is not cool with you calling her “Right_Right_Right_Parent”). We might also run into a problem here when Child finds true love and introduces a Child of their own to the mix - the hierarchy will grow to another level, and I will undoubtedly have to insert another Join into the mix and rename all my fields.
The Problem
Let’s consider a super-relevant hierarchy use case.
Everyone knows that there are 6 degrees of separation between any Hollywood actor and Kevin Bacon. (Do not try to dispute this fact. It is known as “Bacon’s Law,” and it is incontrovertible.) As an illustration of this concept, this article posts a visualization every year of Oscar nominees for Best Actor and Actress and shows the degrees of separation between each nominee and the esteemed Kevin Bacon. As expected per the terms of Bacon’s Law, the journey is short. But how would I show this in data format?
If I start with a list of the Actors from this visualization and include everyone’s Related Actor, this gives me a decent starting point. But how to get from a vertical list of Actor names to a horizontal data depiction of the path to Kevin Bacon for each Oscar nominee??
The Solution
To tackle this problem more dynamically, I introduce you to Alteryx’s special guest star, the Iterative Macro!!
As I was working through solving this for a GL Account roll-up situation (less interesting than family tree or Kevin Bacon hierarchies, but far more applicable to my job), there were a few key problems that needed to be handled by a Hierarchy Macro that would work in every situation:
- Handle any number of hierarchy levels, from 1 to infinity
- Handle Child and Parent columns that were named something other than “Child” and “Parent”
- Output not only the generated hierarchy but all the other fields that were initially input into the macro (to avoid having to join other initial data back to the macro output when complete)
The solution I came up with ended up being an iterative macro nested in a standard macro. The iterative portion helps address item 1 by allowing for any number of hierarchy levels. The macro uses the user-identified Child & Parent columns, Joins Parent to Child to determine the Parent’s Parent, and then outputs to the Iteration Output (as long as there are still records being joined, indicating there are still Parents of Parents to be found).
One of the things that made this a bit trickier was dealing with renaming the columns for each iteration before macro output, to prevent the “Right_Right_Right_Right_Parent” scenario. I ended up using a pair of Dynamic Rename tools to assist with this. For each new iteration, I added 1 to every previous iteration’s column name (so [1] became [2], [2] became [3], etc.). The second Dynamic Rename tool dealt with the fact that by renaming column [1] as column [2] before renaming column [2], there would be duplicate column names, so my newly renamed columns would automatically become [1_2], [2_2], etc. The second Dynamic Rename tool just removed the “_2” from these renamed columns, leaving me with nicely numbered column names once again.
Once I had the iterative portion worked out, I focused on dealing with dynamic column names and the final output of the overall standard Hierarchy Macro. By utilizing the Drop Down Interface tool and choosing the “Fields from Macro Input” option, users can select which field they want to use as the “Child” and which to use as the “Parent.” The macro reassigns the dummy placeholder fields throughout the macro to the user-selected fields. The macro also includes a Join to bring back other fields that were included in the macro input, which will give you a final macro output that includes your original Child field, the hierarchy of Parents (designated by columns named [1], [2], [3] etc. for each level of the hierarchy), and any additional fields that were present in your initial macro input.
By packaging these macros together and running the Actor data through them, I get a nice list output that shows me exactly the path to take to get from Lady Gaga to Kevin Bacon.
The beauty of this macro (besides how easily it translated into Kevin Bacon references) is that it can be used for any hierarchy situation. Need to translate a list of grocery store product categories and subcategories into an easy to read product lineage? No problem. Trying to identify your boss’ boss’ boss so that you can properly elevate your complaints about the wild raccoon your boss is raising in the storage room? Easy-to-read employee hierarchy coming your way. Trying to understand the royal succession path of the British Monarchy? Well you will certainly get an answer with this macro (but let’s be honest, we all know Kate Middleton will be running things.) As long as you can point to a Child and Parent field in your source data, this macro needs no additional configuration and provides a ton of flexibility.
So, the next time your kindergartner needs to put together a family tree, break out the construction paper, the Elmer’s glue…and your Alteryx Hierarchy Generation Macro!
The Hierarchy Generation Macro can be found here.
