Challenge #12: Creating an HR Hierarchy

[GeneR]

Here is this week’s challenge, I would like to thank everyone for playing along and for your feedback. You can find the solution to the previous challenge here.

For this challenge let’s look at creating a multi-level hierarchy from employee-manager data. As always there are several ways to do this challenge, I have designated it as an advanced challenge because there is an elegant way to solve it using iterative macros. The advantage to the iterative macro solution is that it becomes very dynamic. Other hard coded solutions would get you to the answer with this data, but if the depth of the hierarchy were to change, you would have to modify the workflow to support the change. It is a great example to see how iterative macros can make a workflow dynamic.

The use case:

An HR department wants to use Alteryx to quickly understand the reporting structure for employees across their organization.

The Input source contains 5 employees and an identifier that uniquely identifies the individual and the manager they report to.

The goal is to create a hierarchy field identifying each relationship between employee and manager(s). For example, a Director reports directly to the Vice President which is 1 level up. The Director is then 2 levels away from the CEO (in this data set). As a result the hierarchy identifier represents how many levels removed the employee is from management team they report into.

Give it a try, I look forward to your feedback.

UPDATED 2/16/2016:

The Solution has been included.

[TaraM]

The solution to this one has been uploaded - a great example of an Iterative Macro!

[alex]

I worked this one the same way as the solution, except I added a step to sort the employees based on their hierarchy.  I wouldn't have wanted anyone reading the report to see that director is showing above manager since it was sorted alphabetically.  They may assume that the directors report to managers even though that is not what the text of the report shows.  See the non-macro workflow and sorted list below.  Thanks!

[GeneR]

Nice Alex, thanks!

[siddhartha_s]

Certainly a great solution - Now the next level is to build a org tree - using R may be - to make it visually intuitive. Any sugestion how can we get from here to something like:

[GeneR]

Interesting idea @siddhartha_s  , I'm not enough of an R expert to write the code myself to to do this.  I am an Analysts that prefers using the pre-built R functionality in Alteryx.  I did have some fun playing with the network analysis tool, however it always generates a 2-way network so you don't get the nice end nodes like in your screen shot above.  I will reach out to the developers of the Advanced Analytics tools to see if they have any suggestions.

[alex]

I was able to get to this point.  Not exactly what you were asking for, but does visually represent who reports to who.

[GeneR]

I like it @alex

[SeanAdams]

Very similar solution to the provided one - it did bake my brain for a few hours tho' (first time using iterative macros for anything productive).

Main difference was the treatment of Depth and carrying the employees through the flow rather than separating & rejoining (I didn't use the built-in variables of the macro iterator engine thank you for that @GeneR)

Spoiler

[estherb47]

Getting the iterative macro to work was a lot of trial and error with the settings.