Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
gireesh1216
Creator II
Creator II

Need Help on Hierarchy

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 IDRefreshDate13-12-201310-09-201517-09-201718-09-2017
101 0110

 

 

RAW Data:

EmpIDManager IDManager NameEMP Join DateRefreshDate
10150Mexin12-10-201313-12-2013
505101Martin05-09-201510-09-2015
605102Alex12-09-201617-09-2017
50590Jatin05-09-201518-09-2017
50690Jatin05-09-201518-09-2017

 

Required Output:

Manager IDRefreshDate13-12-201310-09-201517-09-201718-09-2017
50 1111
101 0110
102 0011
90 0002

 

 

Thanks

Gireesh

 

Labels (1)
1 Solution

Accepted Solutions
4 Replies
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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

@marcus_sommer 

@swuehl

@sunny_talwar

Please help me on this requirement..