topic Re: Can we create hierarchy level loop in Qlik Sense in App Development

Can we create hierarchy level loop in Qlik Sense

PoojaPrasad — Sat, 16 Nov 2024 19:56:43 GMT

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?

Re: Can we create hierarchy level loop in Qlik Sense

jerifortune — Mon, 07 Oct 2019 20:24:51 GMT

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

Re: Can we create hierarchy level loop in Qlik Sense

asinha1991 — Mon, 07 Oct 2019 20:28:38 GMT

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;

Re: Can we create hierarchy level loop in Qlik Sense

Roadrunner — Mon, 07 Oct 2019 20:34:48 GMT

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

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

Re: Can we create hierarchy level loop in Qlik Sense

PoojaPrasad — Mon, 07 Oct 2019 21:00:37 GMT

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,

Re: Can we create hierarchy level loop in Qlik Sense

Roadrunner — Mon, 07 Oct 2019 21:14:54 GMT

Here is a example of what I was talking about

Re: Can we create hierarchy level loop in Qlik Sense

jerifortune — Tue, 08 Oct 2019 16:34:48 GMT

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;

Re: Can we create hierarchy level loop in Qlik Sense

PoojaPrasad — Fri, 18 Oct 2019 16:46:10 GMT

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,

Re: Can we create hierarchy level loop in Qlik Sense

jerifortune — Fri, 18 Oct 2019 17:33:02 GMT

Hi PoojaPrasad,

I am glad this helped.

Thank you for the feedback.

Re: Can we create hierarchy level loop in Qlik Sense

PoojaPrasad — Mon, 21 Oct 2019 16:41:41 GMT

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.

Re: Can we create hierarchy level loop in Qlik Sense

jerifortune — Mon, 21 Oct 2019 19:17:28 GMT

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:

Re: Can we create hierarchy level loop in Qlik Sense

jerifortune — Mon, 21 Oct 2019 19:38:03 GMT

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

Re: Can we create hierarchy level loop in Qlik Sense

PoojaPrasad — Mon, 11 Nov 2019 22:51:57 GMT

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.

Re: Can we create hierarchy level loop in Qlik Sense

jerifortune — Tue, 12 Nov 2019 15:57:25 GMT

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;