Displaying Data in Hierarchy in Pivot or Straight Table

[Anonymous]

Hi all,

I have a requirement as below

Consider i have 3 fields Department, Manager and Employee

I have hierarchy of managers and employees to be displayed in a table

Here a Manager named 'A'  can have 1 or more employee working under him, and that manager 'A' can come under in Employees list also, as that Manager 'A' is working under another Manager 'B' . Similarly it can be any no of Managers under Managers field and as well as in Employees list also, as there are higher level Managers for them also. And one employee can also have more than one Manager

Example :

Dept                Manager     Employee      Emp credit

health                   A                 E                    10

health                   A                 F                     20

sports                   B                 A                    10

sports                   B                 F                    20

sports                   B                 G                    30

accounts              C                 B                    10

finance                 D                 C                    20

finance                 D                 H                    10

health                   E                 I                      10

The above is the raw data which i get. I need to display Managers and employees in a hierarchy as below,

where Here

Level 1 : Manager 'D' is the Head, C and H are working under him

Level 2 : Manager is 'C'  =>  B is working under him.

Level 3 : Manager is 'B'  =>  A, F, G are working under him.

Level 4 : Manager is A   =>  E, F are working under him.

Level 5 : Manager is F   =>  I is working under him.

So it can be any no of managers and employees work under him.

Help me in this.

swehul‌ sunny talwar‌ kushal chawda‌ marcus_sommer‌ avinash r‌ gysbert wassenaar‌ marco wedel‌

I also have attached the application with sample data

Thank you all.

[Sergey_Shuklin]

Hello, Kishore!

I've found this solution:

If it's what you are lookin' for, so do the followin' steps:

1. Use below script with Hierarchy() load:

Data:
LOAD Dept, Manager, Employee, Empcredit Inline
[
Dept,        Manager,     Employee,      Empcredit
health,       A,            E,              10
health,       A,            F,              20
sports,       B,            A,              10
sports,       B,            F,              20
sports,       B,            G,              30
accounts,     C,            B,              10
finance,      D,            C,              20
finance,      D,            H,              10
health,       E,            I,              10
health,       A,            I,              40
];

Hierarchy:
Hierarchy(Employee,Manager,Dept)
LOAD AutoNumber(Dept&'_'&Employee&'_'&Manager) as %key,
Employee,Manager,Dept Resident Data;

fact:
LOAD AutoNumber(Dept&'_'&Employee&'_'&Manager) as %key
,Empcredit Resident Data;

temp:
LOAD distinct Manager Resident Data;

levels:
LOAD Manager, 'level '&RowNo() as level Resident temp Order By Manager;

DROP Table temp;
DROP Table Data;

2. Create a pivot table with 3 dimensions:

3. Make an expression which will show employees. I used Concat() function, it can also be MaxString() or smth else.

I also used the second pivot table for creating a total header. It's the easiest way I think.

[Anonymous]

thanks for spending your time on this.

But i need the hierarchy as exactly as i have attached in the image .

Here D is the top most head. He should come first as in the image.