Hierarchy issue in the pivot table

[sumanta1234]

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:

[Vegar]

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]

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]

Hi @Vegar 

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