Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
sumanta1234
Partner - Creator
Partner - Creator

Hierarchy issue in the pivot table

Hi 

I have data like below:

Dept

Reporting Mgr

Employee

DEPT1

Rohit Sharma

Pranab Pandey

DEPT1

Rohit Sharma

Rakesh Das

DEPT1

Rohit Sharma

Rahul Mandal

DEPT2

Rohit Sharma

Tarak Basak

DEPT1

Pranab Pandey

Sishir Das

DEPT1

Pranab Pandey

Rohit Roy

DEPT1

Pranab Pandey

Pappu Kedia

DEPT1

Pranab Pandey

Mihir Sen

DEPT1

Rohit Roy

Montu Mandal

DEPT1

Pappu Kedia

Sumanta Naskar

DEPT2

Tarak Basak

Rabin Roy

DEPT1

Joe Warren

Rohit Sharma

 

I want to show the data in the qliksense pivot table. When I will select Rohit Sharma it will show the whole hierarchy under him:

sumanta1234_0-1604643735346.png

 

Labels (3)
3 Replies
Vegar
MVP
MVP

I think you will need to model this in the script. Take a look at the hierarchy() funktion.

https://help.qlik.com/sv-SE/sense/June2020/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes...

sumanta1234
Partner - Creator
Partner - Creator
Author

Hi,

I have the below script but it is not showing the full hierarchy.

ORGANIZATION:
LOAD Dept,
Reporting Mgr,
Employee,
Value
FROM
[lib://Connection/Hierarchy.xlsx]
(ooxml, embedded labels, table is Sheet1);

TREE_VIEW:
LOAD Distinct
Employee as VALUE,
Employee & '-Employee' as NODE_ID,
Reporting Mgr & '-Reporting Mgr' as PARENT_NODE_ID
Resident ORGANIZATION;
LOAD Distinct
Reporting Mgr as VALUE,
Reporting Mgr & '-Reporting Mgr' as NODE_ID,
Dept & '-Dept' as PARENT_NODE_ID
Resident ORGANIZATION;
LOAD Distinct
Dept as VALUE,
Dept & '-Dept' as NODE_ID,
Dept & '-Dept' as PARENT_NODE_ID
Resident ORGANIZATION;

HIERARCHY_TABLE:
Hierarchy(NODE_ID,PARENT_NODE_ID, NODE, PARENT_NAME, NODE, Path,'/',depth) load
NODE_ID,
PARENT_NODE_ID,
VALUE as NODE
Resident TREE_VIEW;

ORG_FINAL:
LOAD
User AS NODE3,
Dept AS DEPARTMENT,
Val AS VALUE
RESIDENT ORGANIZATION;

DROP TABLES TREE_VIEW,ORGANIZATION;

 

sumanta1234
Partner - Creator
Partner - Creator
Author

Hi @Vegar 

Could you please provide the script to get the proper output.