I am trying to create an employee hierarchy from a dataset that is provided in the following format (example):
Employee Name | Supervisor Employee Number | Supervisor Name | Level 2 Supervisor Name | Level 3 Supervisor Name | Level 4 Supervisor Name | Level 5 Supervisor Name | Level 6 Supervisor Name | Supervisor Category |
Ram | 1 | Keith | Hugh | Mike | Keith | |||
Stephen | 2 | Ram | Hugh | Mike | Keith | Ram | ||
Annika | 3 | Ram | Hugh | Mike | Keith | Ram | ||
John | 4 | Kerri | Hugh | Mike | Keith | Kerri | ||
Roger | 5 | John | Hugh | Mike | Keith | Kerri | John | |
Jonathan | 6 | John | Hugh | Mike | Keith | Kerri | John | |
Stephanie | 7 | John | Hugh | Mike | Keith | Kerri | John | |
Miguel | 8 | Ram | Hugh | Mike | Keith | Ram | ||
William | 9 | Ram | Hugh | Mike | Keith | Ram | ||
Debby | 10 | Kirsi | Hugh | Mike | Keith | Kirsi | ||
Kerri | 11 | Keith | Hugh | Mike | Keith | |||
Brian | 12 | Kerri | Hugh | Mike | Keith | Kerri | ||
Hugh | 13 | |||||||
Mike | 14 | Hugh |
My goal is to put each employee into a category as follows:
1.) Employee has multiple supervisees.
2.) Employee has one supervisee.
3.) Employee has no supervisees.
I would like to populate the far right column with a 1, 2, or 3 depending on how many supervisees the employee has.
Does anyone have any ideas for how I can do this?
Solved! Go to Solution.
Hi,
There are a few ways to interpret this, so I have outlined a couple options
If you only care about Direct Reports:
Use a Summarize tool, Group by Supervisor Name, Count Distinct Employee Name. Then you can Join the data back together on Employee Name = Supervisor Name, and create the categories using a Formula tool.
Your formula might look something like:
IF [CountDistinct_Employee Name] >1 THEN 1 ELSEIF [CountDistinct_Employee Name] = 1 THEN 2 ELSE 3 ENDIF
If you are looking for the total number of employees under a given individual (including indirect reports), then you will want to use a Transpose tool to start things off, to put all of the "Level # Supervisor Name" columns into one set of values. Then you can summarize on this and use a similar method to the Direct Report method.
Hope this helps!