Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
PoojaPrasad
Creator II
Creator II

Can we create hierarchy level loop in Qlik Sense

Hello Experts,

I have a csv file that has following information:

emp id, emp name, manager id

1, A, -

2, B, A

3, C, B

4, D, C

I understand that using hierarchy I can create depth 

emp id, emp name, manager id, Path Name, depth

1, A, -, A, 1

2, B, A,A/B, 2

3, C, B, A/B/C, 3

4, D, C, A/B/C/D, 4

I need a filter condition where if I select emp- A and level as 1, I should only get B

If I select emp-A and level 2, I should get B and C

If I select emp B and level 3, I should get B, C and D

Any idea how can I implement this?

Labels (1)
13 Replies
jerifortune
Creator III
Creator III

Not really sure what you are trying to achieve, but this should guide you

Adjust your script to include

 

FOR i=1 to $(vNofEmpLevel)
NewEmployeeTbl:
LOAD empid, empname$(i) AS LineManagers, $(1) AS Level
Resident Hiertest
;

NEXT;


Data:
Load empid,LineManagers, Level  Resident Temp_data
Where empname <> LineManagers AND Len(LineManagers) >0
;

LEFT JOIN (Data)
LOAD Max(Level) AS maxLevel,
empid
Resident Data
GROUP BY empid
;

Dataset:

LOAD empid,LineManagers,IF(flag=0, 'Direct Report', flag & ' Level Below') AS Desc
;
LOAD empid,LineManagers, num(maxLevel)-num(Level) As flag
Resident Data ;

Drop Table Data:

jerifortune
Creator III
Creator III

You need append the previous script. I only added Dataset table. 

PoojaPrasad
Creator II
Creator II
Author

Hello @jerifortune ,

I copied the code given above:

 

[Hierarchy]:
Noconcatenate
Hierarchy(NodeID, ParentID, NodeName, ParentName, NodeName, PathName, '\', Depth)
LOAD
num(EmployeeID) as NodeID,
if([CustomMgrID] = '-', '',CustomMgrID) as ParentID,
Fullname as NodeName
Resident Customer_Managers_withID;

tempMaxLevel:
LOAD MAX(Depth) AS #ofEmpLevel Resident Hierarchy;
Let vNofEmpLevel= Peek('#ofEmpLevel',0,'tempMaxLevel');

FOR i=1 to $(vNofEmpLevel)
NewEmployeeTbl:
LOAD NodeID, NodeName$(i) AS LineManagers , $(1) AS Level
Resident Hierarchy;
//Resident Hiertest;

NEXT;
NoConcatenate

Temp_data:
Load * Resident NewEmployeeTbl;
Left Join (Temp_data)

Load NodeID, NodeName Resident Hierarchy;

Drop table NewEmployeeTbl;
NoConcatenate
Data:
Load NodeID,LineManagers Resident Temp_data
Where NodeName <> LineManagers AND Len(LineManagers) >0
;

LEFT JOIN (Data)
LOAD Max(Level) AS maxLevel,
NodeID
Resident Data
GROUP BY NodeID
;


Dataset:

LOAD NodeID,LineManagers,IF(flag=0, 'Direct Report', flag & ' Level Below') AS Desc
;
LOAD NodeID,LineManagers, num(maxLevel)-num(Level) As flag
Resident Data ;
Drop Table Data;

Drop Table Temp_data;
//Drop table [Customer_Managers_withID];
exit script;

 

But I am getting the error as below:

hierarchy_error.PNG

Honestly speaking I did not understand the code completely so not able to fix the error.

My use case is: Two columns in filter: Line_Manager & Levels_Below

So, if an employee in  Line_Manager is selected (say he/she is on depth 3), the column Levels_Below should show depth 4 under him as Direct Reportee, 5 as Level 1 and 6 as Level 3 and so on.

If an employee in  Line_Manager is selected (say he/she is on depth 4), the column Levels_Below should show depth  5 under him as Direct Reportee, 6 as Level 1 and 7 as Level 3 and so on.

jerifortune
Creator III
Creator III


Hiertest:
Hierarchy
(empid, managerid, empname, Manager, empname , Employee_Hierarchy,,[Depth])
Load
empid, managerid, empname
;
LOAd * Inline [
empid, empname, managerid

1, A, -

2, B, 1

3, C, 2

4, D, 3
]
;


tempMaxLevel:
LOAD MAX(Depth) AS #ofEmpLevel Resident Hiertest;
Let vNofEmpLevel= Peek('#ofEmpLevel',0,'tempMaxLevel');

FOR i=1 to $(vNofEmpLevel)
NewEmployeeTbl:
LOAD empid, empname$(i) AS LineManagers, $(i) AS Level
Resident Hiertest
;

NEXT;

NoConcatenate

Temp_data:
Load * Resident NewEmployeeTbl;
Left Join (Temp_data)

Load empid, empname Resident Hiertest;

Drop table NewEmployeeTbl;
NoConcatenate

tempData:

Load empid,LineManagers, Level Resident Temp_data
Where empname <> LineManagers AND Len(LineManagers) >0
;
LEFT JOIN (tempData)
LOAD Max(Level) AS maxLevel,
empid
Resident tempData
GROUP BY empid
;


Data:
LOAD empid,LineManagers,IF(flag=0, 'Direct Report', flag & ' Level Below') AS Desc
;
LOAD empid,LineManagers, num(maxLevel)-num(Level) As flag
Resident tempData ;

Drop Table Temp_data, tempData;

Drop table Hiertest;