Flat Hierarchy to a Tabular format

[belouizi]

I hope someone can help me with this as I tried for a few days now but with no desirable outcome. Thank you in advance

Here is my current state:

EE_ID	Manager_ID	Hierarchy_BottomUp	Hierarchy_TopDown
111	101	10	1
111	102	9	2
111	125	8	3
111	215	7	4
111	270	6	5
111	1257	5	6
111	21510	4	7
111	43543	3	8
111	165231	2	9
111	14214	1	10
222	101	4	1
222	1218	3	2
222	132854	2	3
222	3223	1	4

Desire outcome below:

Note: Employee 111 is  a regular employee where  222 is a supervisor; that's why EE_SupvTD_Level for 111 = 0 and 222 = 5. Basically their level in the top down supervisory hierarchy.

EE_ID	Manager_ID	Supv Count	EE_SupvTD_Level	Supv_BU10	Supv_BU09	Supv_BU08	Supv_BU07	Supv_BU06	Supv_BU05	Supv_BU04	Supv_BU03	Supv_BU02	Supv_BU01	Supv_TD01	Supv_TD02	Supv_TD03	Supv_TD04	Supv_TD05	Supv_TD06	Supv_TD07	Supv_TD08	Supv_TD09	Supv_TD10
111	101	10	0	101	102	125	215	270	1257	21510	43543	165231	14214	101	102	125	215	270	1257	21510	43543	165231	14214
222	101	4	5							101	1218	132854	3223	101	1218	132854	3223

[Thableaus · Accepted solution]

Hi @belouizi 

Here's a solution:

The only thing you need to additionally do is to specify a flag indicating if the employee is a regular one or a supervisor, so we can create "EE_SupvTD_Level" column.

Workflow attached on version 2018.4

Cheers,

[belouizi]

Thank you so much. IT WORKED PERFECT!!

In fact, we don't need "EE_SupvTD_Level" column any more.

would it be possible to replace the levels by a different verbiage as below? 

Supv_TD01 = CEO
Supv_TD02 = DRG
Supv_TD03 = DRG-1
Supv_TD04 = DRG-2
Supv_TD05 = DRG-3
Supv_TD06 = DRG-4
Supv_TD07 = DRG-5
Supv_TD08 = DRG-6
Supv_TD09 = DRG-7
Supv_TD10 = DRG-8

[belouizi]

I was able to do it using the select tool. that was easy enough. Thanks again