topic Re: Displaying Data in Hierarchy in Pivot or Straight Table in QlikView

Displaying Data in Hierarchy in Pivot or Straight Table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Displaying Data in Hierarchy in Pivot or Straight Table

Sergey_Shuklin — Sun, 24 Sep 2017 15:07:51 GMT

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.

Re: Displaying Data in Hierarchy in Pivot or Straight Table

Anonymous — Mon, 25 Sep 2017 04:23:08 GMT

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.