Need Help on Hierarchy

[gireesh1216]

Hello Team,

I have Employee data and mangers list like below. If Manager changes happens to the EmpID,the below table will update.

Requirement: We need employees counts (Count(EmpID)) based on Manager ID, Refresh Date. Please check required Output table below. 

Example : Emp id 505, Joined date is 05-09-2015 ,manager ID is 101 and database refresh date is '10-09-2015'.

For same Emp ID 505 and his manager changed at 18-09-2017.

So we have to assume for  EmpID 505  and his OLD manager ID 101 is from (10-09-2015 TO 17-09-2017) , here we have to calculate EMP ID count like below.

Manager ID	RefreshDate	13-12-2013	10-09-2015	17-09-2017	18-09-2017
101		0	1	1	0

RAW Data:

EmpID	Manager ID	Manager Name	EMP Join Date	RefreshDate
101	50	Mexin	12-10-2013	13-12-2013
505	101	Martin	05-09-2015	10-09-2015
605	102	Alex	12-09-2016	17-09-2017
505	90	Jatin	05-09-2015	18-09-2017
506	90	Jatin	05-09-2015	18-09-2017

Required Output:

Manager ID	RefreshDate	13-12-2013	10-09-2015	17-09-2017	18-09-2017
50		1	1	1	1
101		0	1	1	0
102		0	0	1	1
90		0	0	0	2

Thanks

Gireesh

[Kushal_Chawda]

I don't think you need hierarchy function.  Try using below

T1:
LOAD EmpID,
    "Manager ID",
    "Manager Name",
    "EMP Join Date",
    RefreshDate
FROM [lib://Data/Data.qvd]
(qvd);

Left Join(T1)
Load EmpID,
     Count(RefreshDate) as Refresh_Date_Count
Resident T1
Group by EmpID;

Max_Refresh_Date:
Load date(Max(RefreshDate)) as Max_Refresh_Date;
Load FieldValue('RefreshDate',RecNo()) as RefreshDate
AutoGenerate FieldValueCount('RefreshDate');

let vMaxDate = Peek('Max_Refresh_Date',0,'Max_Refresh_Date');

T2:
Load EmpID,
    "Manager ID",
    "Manager Name",
    "EMP Join Date",
     RefreshDate,
     RefreshDate as Min_Date,
     alt(if(Refresh_Date_Count>1,peek(RefreshDate)),'$(vMaxDate)') as Max_Date
Resident T1
Order by RefreshDate desc;

Drop Table T1;

Drop Field RefreshDate;

T3:
NoConcatenate
Load  EmpID,
    "Manager ID",
    "Manager Name",
    "EMP Join Date",
     date(Min_Date + IterNo()-1) as RefreshDate
Resident T2
While Min_Date + IterNo()-1<=Max_Date;

Inner Join(T3)
Load Distinct Min_Date as RefreshDate
Resident T2; 

Drop Table T2;

Now you can create  pivot table with measure count(EmpID)

[gireesh1216]

Hello Kush,

Thanks for your replay 🙂 

The above code is working only for one time update records and not working for multiple update records.

Can you please help me on this requirement. 

For requirement understanding -please call or whats up :+91 9611884697

[gireesh1216]

@marcus_sommer 

@swuehl

@sunny_talwar

Please help me on this requirement.. 

[Kushal_Chawda · Accepted solution]

Is it same question in below link? have a look at my reply there

https://community.qlik.com/t5/Qlik-Sense-App-Development/Hierachy-in-qlik-sense/m-p/1730631/highligh...