Can we create hierarchy level loop in Qlik Sense

[PoojaPrasad]

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?

[jerifortune]

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]

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

[PoojaPrasad]

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:

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 · Accepted solution]

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;