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 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]

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]

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]

I am trying to implement filter as shown below:

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]

Here is a example of what I was talking about

[jerifortune · Accepted solution]

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;

[PoojaPrasad]

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]

Hi PoojaPrasad,

I am glad this helped.

Thank you for the feedback.

[PoojaPrasad]

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.