Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ 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 (2)
2 Solutions

Accepted Solutions
jerifortune
Creator III
Creator III

This will give you the Managers under each employee. After executing the script, create a  a table chart with Empid and LineManagers.

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
Resident Hiertest
;

NEXT;

NoConcatenate

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

Load empid, empname Resident Hiertest;

Drop table NewEmployeeTbl;
NoConcatenate
Data:
Load empid,LineManagers Resident Temp_data
Where empname <> LineManagers
;
Drop Table Temp_data;

View solution in original post

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;

View solution in original post

13 Replies
jerifortune
Creator III
Creator III

Not sure what you are trying to achieve but hierarchy level allows you to see  employee BCD when you select Level 2,  CD when you select Level 3, and D when you select Level 4.

Level 1 can give you Employee ABCD.  However, you can write a script to adjust this behavour

 

asinha1991
Creator III
Creator III

maybe you need something like this  and then you can play with it(create crosstable etc) depending on how you want to create filters

 


PRE:
Hierarchy(NodeID, ParentID, NodeName, ParentName, NodeName, PathName, '\', Depth) LOAD * inline [
NodeID,NodeName,ParentID
1, A,''
2, B,1
3, C,2
4, D,3
];


POST:
Load if(not isnull(NodeName4),NodeName1) as Level4
, if(not isnull(NodeName4),NodeName2,if(not isnull(NodeName3),NodeName1)) as Level3,
if(not isnull(NodeName4),NodeName3,if(not isnull(NodeName3),NodeName2,if(not isnull(NodeName2),NodeName1))) as Level2 ,NodeName as Emp Resident PRE;


Drop table PRE;

Roadrunner
Contributor III
Contributor III

Hello,

Sometimes i find it easier to build in a csv table that I load.

I have one table that is "Current Alignment" and then another that is "Historical Alignment"

This way I can look at data, like financials,  based on current set of stores that a person is responsible for or look back at their Historical data.

I use the following:

VPO

RDO

DM

GM

and then each is broke out by the financial period ( time frame ) that they were responsible for

 

PoojaPrasad
Creator II
Creator II
Author

I am trying to implement filter as shown below:

hierarchy_requirement.png

 

If a particular emp_id is selected and level is selected, I should get all employees who report directly, 2 below, 3 below and so on to Emp_ID

Thank you,

Roadrunner
Contributor III
Contributor III

Here is a example of what I was talking about

1.png

jerifortune
Creator III
Creator III

This will give you the Managers under each employee. After executing the script, create a  a table chart with Empid and LineManagers.

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
Resident Hiertest
;

NEXT;

NoConcatenate

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

Load empid, empname Resident Hiertest;

Drop table NewEmployeeTbl;
NoConcatenate
Data:
Load empid,LineManagers Resident Temp_data
Where empname <> LineManagers
;
Drop Table Temp_data;

View solution in original post

PoojaPrasad
Creator II
Creator II
Author

Hello Jerifortune,

It took me a while to implement the same on my real data set but your code does give me Line Managers and all his/her levels in Depth. I do not have in depth knowledge of coding on Qlik and but your code gave an idea on how easily we can implement queries using functions in qlik. Thank you so much.

Best Regards,

jerifortune
Creator III
Creator III

Hi PoojaPrasad,

I am glad this helped.

 

Thank you for the feedback.

PoojaPrasad
Creator II
Creator II
Author

Hello Jerifortune,

I have one quick question. Is it possible to customize the display value of the depth corresponding to line managers? Say if a line manager at depth 3 is selected, then under his hierarchy - depth 4 should be shown as direct report, 5 as 2 level below, 6 as 3 level below and so on. This should happen for every selected line managers as per the depth he/she resides.