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

Engine Works Blog

Under the hood of Alteryx: tips, tricks and how-tos.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
Magnetar
Magnetar

* 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:

 

clipboard_image_0.png

 

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.

 

clipboard_image_1.pngWhat things are included in The Top Thing? All the Things.

 

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

 

clipboard_image_2.png"Thank you for the hand-knit onesie pajamas, Parent of Parent!"

 

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.

 

clipboard_image_3.pngA sad, inefficient workflow that doesn’t easily grow when Child has an offspring (No hand-knit onesie for you, Junior!)

 

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?

 

clipboard_image_4.jpeg

 

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??

 

clipboard_image_5.pngVertical lists are boring.

 

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:

 

  1. Handle any number of hierarchy levels, from 1 to infinity
  2. Handle Child and Parent columns that were named something other than “Child” and “Parent”
  3. 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.

 

clipboard_image_6.pngThe Hierarchy Iterative macro. Building Family Trees since 2019.

 

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.

 

clipboard_image_7.pngSo many Children and Parents in this macro, it’s basically a Saturday afternoon at Chuck E. Cheese!

 

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.

 

clipboard_image_8.pngTell me something, boy…ain’t Kevin Bacon just so hardcore?

 

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.

Comments
Meteor

This macro is awesome - having the logic be dynamic to the number of levels is great.  One thing I noticed is that if your parent-child fields are represented as integer (id) fields then the macro breaks with what appears to be a type conversion error. 

Was able to work-around by casting them to a string value first. Not a big deal but thought I would point out as maybe something for the backlog/next version- thank you for creating this resource!

Alteryx
Alteryx

How does this only have 4 stars?

 

Oh, I didn't realize it was new today. I predict the number of stars will continue to grow!

Quasar
Quasar
Thanks for this article Nicole! I'm sending it to my finance teams since they are always trying to solve similar problems.
Asteroid
Asteroid

NJ, 

 

I'll be dropping the gauntlet by throwing the General Motors organizational hierarchy into this beautiful work of art. I'll let you know how it performed.

 

Thanks for sharing your clever work because I have been too lazy to do this! Awesome job.

 

Ken

Asteroid

Super useful..specially when you are using Essbase.Thanks for this wonderful macro.

Bolide

@NicoleJohnson Have you used this macro on an Org Structure? We generate org structures through Blueprint(Now Diligent). I'm curious as I built something (not nearly as robust) by similar for Tax Reform Limitations (although Kevin Bacon is more interesting).

 

Thanks,

Seth

Magnetar
Magnetar

Fabulous article and awesome macro, @NicoleJohnson !!!!

 

Added tidbit. If you've met me, you're only 3 degrees from Kevin Bacon.

 

I was in a play ("Candida" at the Hangar Theatre) with Billy Crudup, who was in "Sleepers" with Kevin Bacon.

 

There you have it!

 

Cheers,

Esther

Pretty amazing what you've created here, @NicoleJohnson. Any idea how long this would be expected to take to run on an org chart containing about 14,000 individuals?

Magnetar
Magnetar

@Whodathunkit - Give it a whirl! I've run it against data sets with thousands of records and it seemed to generate the output no problem, in just a matter of seconds... will definitely depend on how clean your data is, however, so make sure you check for duplicate children (i.e. children with more than one parent)... otherwise, you should be good! Let us know how it goes!!

@NicoleJohnson so what you're saying is 2.5 hours isn't to be expected? LOL. It's been stuck at 28% on the Macro tool for pretty much that entire time. Our org shouldn't go that deep, so I'm guessing I must not have configured something correctly somewhere.

Magnetar
Magnetar

@Whodathunkit haha no, that should not be happening... check to make sure your child field is unique first (could throw a Unique tool right before it). You might also limit the fields coming into it - shouldn’t make a difference but it’s worth a shot??

 

Sorry it didn’t work the first time!! Kevin Bacon only has so much magic in the bank I suppose... message me directly if it still doesn’t work. 🙂

 

Cheers,

NJ

@NicoleJohnson, DANGGGG. You're good. There were a few nulls in my child field, for who knows what reason. Filtered those out and threw on a unique for good measure, and BAM! 4.4 seconds later...Org chart with hierarchy. You are a miracle worker. Thanks for your time, not only for our back and forth, but for creating this wonderful piece of art. 

Asteroid

This is great! Now I just need support for reading and writing Gedcom files!

Asteroid

This is really a great solution. I need to create the Hierachy from Essbase source and I hope this will server the purpose. 

Temporaritly I created one solution for dealing with the data to create the hierachy (each level with +5 spaces) to columns. I used 16 level (16 back to back Multi-row formula tools) and used it further, as I know that hierachy in my data set won't go beyond that. But infinite hierarchy for data is really awesome. I am gonna try it. Thanks for this solution.  🙂

Spoiler
Hierarchy.JPG
Labels