community
cancel
Showing results for 
Search instead for 
Did you mean: 
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.
Getting started with Designer? | Start your journey with our new Learning Path!

Challenge #12: Creating an HR Hierarchy

Alteryx Alumni (Retired)

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.

Creative Director
Creative Director

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

Tara McCoy
Bolide

 

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!

 

week12workflow.PNG

 

week12workflow2.PNG

Alteryx Alumni (Retired)

Nice Alex, thanks!

Highlighted

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:

 

Capture.PNG

Alteryx Alumni (Retired)

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.

Bolide

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

 

wk12diagram.PNG

Alteryx Alumni (Retired)

I like it @alex

Nebula
Nebula

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

MacroPic.png

Pulsar
Pulsar

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