Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
NicoleJohnson
ACE Emeritus
ACE Emeritus

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

 

What things are included in The Top Thing? All the Things.What 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.)

 

"Thank you for the hand-knit onesie pajamas, Parent of Parent!""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.

 

A sad, inefficient workflow that doesn’t easily grow when Child has an offspring  (No hand-knit onesie for you, Junior!)A 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??

 

Vertical lists are boring.Vertical 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.

 

The Hierarchy Iterative macro. Building Family Trees since 2019.The 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.

 

So many Children and Parents in this macro, it’s basically a Saturday afternoon at Chuck E. Cheese!So 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.

 

Tell me something, boy…ain’t Kevin Bacon just so hardcore?Tell 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.

Nicole Johnson
Sr. Product Manager, Designer Desktop

2018 Alteryx Grand Prix Champion | 2018 Alteryx ACE Cohort | Alteryx Expert Certification | Weekly Challenge Addict | Seattle-Eastside Alteryx User Group Leader | Self-proclaimed Data Therapist

2018 Alteryx Grand Prix Champion | 2018 Alteryx ACE Cohort | Alteryx Expert Certification | Weekly Challenge Addict | Seattle-Eastside Alteryx User Group Leader | Self-proclaimed Data Therapist

Comments
danners
7 - 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!

TonyaS
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!

dataMack
12 - Quasar
Thanks for this article Nicole! I'm sending it to my finance teams since they are always trying to solve similar problems.
Ken_Black
9 - Comet
9 - Comet

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

rajvivan11
8 - Asteroid

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

smoskowitz
12 - Quasar

@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

estherb47
15 - Aurora
15 - Aurora

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

Whodathunkit
7 - Meteor

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?

NicoleJohnson
ACE Emeritus
ACE Emeritus

@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!!

Whodathunkit
7 - Meteor

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

NicoleJohnson
ACE Emeritus
ACE Emeritus

@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

Whodathunkit
7 - Meteor

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

tonypreece
10 - Fireball

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

TarunDeep
8 - 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
as683513
5 - Atom
Hi @NicoleJohnson do you know of a way to alter the workflow to repeat Parent values so you can see the highest level and every child underneath? Output Example: Lvl1 Lvl2 Lvl3 Lvl4 CEO President East VP East Director 1 East Manager 1 CEO President East VP East Director 2 East Manager 2 CEO President East VP East Director 3 East Manager 3 CEO President East VP East Director 4 East Manager 4
NicoleJohnson
ACE Emeritus
ACE Emeritus

@as683513 - you should be able to do this with a few Preparation & Transform after the Hierarchy macro to get the data in your desired format. See an screenshot below that takes the standard output from the macro and pivots it to something that I think looks more like what you're looking for. 

 

Unfortunately I can't attach something to a blog comment... but I tried to include enough in the annotations below to point you in the right direction 🙂

NicoleJohnson_0-1579902141992.png

Cheers!

NJ

as683513
5 - Atom
Thank you!!
sducey95
6 - Meteoroid

Hi Nicole,

 

This is amazing and will be really helpful. Is there something specifically in the workflow that is not allowing me to import on Alteryx version 2018.3?

 

Working to get Alteryx 2019 installed as well, but I was just making sure this error would make sense.

 

Thanks!

jmarkham002
5 - Atom

@NicoleJohnson, this is great. You mentioned in another post that having duplicate children (i.e. children with more than one parent) could cause issues. Is there a way to reconfigure to allow a child to have more than one parent (e.g. split ownership)? I deal a lot of company entity charts (i.e. parent-subsidiary relationships), and often times a subsidiary may have more than one owner (e.g. a partnership). Would it be possible for the macro to follow each path to the ultimate parent? I found another iterative macro on another thread (https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Break-Hierarchy-into-multiple-levels/t...) that seemed to work that out. What I like about yours is getting to retain that non-parent child data with each child which in my case would be say ownership %.


For example,

 

1 owns 100% of 2, 2 owns 100% of 3, 2 owns 20% of 4, 3 owns 80% of 4, 4 owns 100% of 5. 

The path for 5 to 1 has two paths:

 

Path 1) 5-4 (100%), 4-3 (80%), 3-2 (100%), and 2-1 (100%)

Path 2) 5-4 (100%), 4-2 (20%), 2-1 (100%)

 

So in Path 1, thing 5 is a level 5 child (thing 1 being level 1), and in Path 2 thing 5 is a level 4 child.

 

Any help would be greatly appreciated!

jacob_kahn
12 - Quasar

Amazing!

mceleavey
17 - Castor
17 - Castor

*Quietly sneaks in*

 

*steals more of @NicoleJohnson 's work*

 

*leaves chocolates on the table*

 

*sneaks out*

alecbmuller
5 - Atom

Incredible work! Added bonus of using my favorite Actor! Thank you Nicole!

AbhilashR
15 - Aurora
15 - Aurora

Thank you for making our lives easy! I was using a bunch of self joins to parse HR data this morning and am glad I found your macro.

Shaaz
9 - Comet

Hi,

 

Is it possible to achieve the reverse ?

 

like whatever we are getting as output data from this macro, can we revert back and get the input data ?... dynamically.

 

For example: I have the input data exactly as output of this macro, Can I achieve input from this dynamically ?

barasrij
5 - Atom

Hi, 
I've been trying to use this macro but I'm a beginner to Alteryx.

 

Can someone let me know how can I select the child and parent field for my data?

The tools immediate next to the input tool in container are not familiar to me.

paliio
5 - Atom

Hi Nicole, the solution is amazing and extremely useful, thank you a lot! I faced the same issue with building hierarchy and your macros helped me a lot. However, can you or smn in the community advise on one issue I face:

My data has parents, children and also countries of Parents. For instance,

 

Child_1 ''A'' has parent_1 ''BB'' and country_parent1 ''UK''

Child_2 ''BB'' has parent ''CC'' and country_parent2 ''SG"

 

I need to have hierarchy including countries like below:

 

child_1 "A" parent ''BB'' country_parent1 ''UK'' parent of parent "BB" is ''CC'' and country_parent2 is ''SG" and so on

 

Does anyone have an idea how to add countries to the logic?

 

Thank you!