Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
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..