HELP: Creating an employee hierarchy from a dataset

Question

I am trying to create an employee hierarchy from a dataset that is provided in the following format (example):

Employee NameSupervisor Employee NumberSupervisor NameLevel 2 Supervisor NameLevel 3 Supervisor NameLevel 4 Supervisor NameLevel 5 Supervisor NameLevel 6 Supervisor Name Supervisor CategoryRam 1Keith HughMikeKeith   Stephen 2Ram HughMikeKeithRam  Annika 3Ram HughMikeKeithRam  John 4Kerri HughMikeKeithKerri  Roger5John HughMikeKeithKerriJohn  Jonathan 6John HughMikeKeithKerriJohn  Stephanie 7John HughMikeKeithKerriJohn  Miguel 8Ram HughMikeKeithRam  William9Ram HughMikeKeithRam  Debby 10Kirsi HughMikeKeithKirsi  Kerri11Keith HughMikeKeith   Brian 12Kerri HughMikeKeithKerri  Hugh13       Mike14 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?

Claje · Accepted Answer

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!