topic Re: Need Help on Hierarchy in QlikView

Need Help on Hierarchy

gireesh1216 — Sat, 16 Nov 2024 00:15:38 GMT

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

Re: Need Help on Hierarchy

Kushal_Chawda — Sat, 25 Jul 2020 21:01:38 GMT

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)

Re: Need Help on Hierarchy

gireesh1216 — Mon, 27 Jul 2020 10:21:21 GMT

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

Re: Need Help on Hierarchy

gireesh1216 — Mon, 27 Jul 2020 10:32:57 GMT

@marcus_sommer

@swuehl

@sunny_talwar

Please help me on this requirement..

Re: Need Help on Hierarchy

Kushal_Chawda — Tue, 28 Jul 2020 15:39:27 GMT

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/highlight/false#M55712