Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need some help on the below requirement in Qlik sense. I tried with Hierarchy, but some gap.
Employee:
Manager ID | Emp Id | Manager Name | Emp Join date | Refresh Date | Remarks |
50 | 101 | Mexin | 12-10-2013 | 13-12-2013 |
|
101 | 505 | Martin | 05-09-2015 | 10-09-2015 |
|
102 | 605 | Alex | 12-09-2016 | 15-10-2016 |
|
103 | 405 | Anand | 16-09-2016 | 16-10-2016 |
|
90 | 505 | Jatin | 05-09-2015 | 18-09-2017 | 505 employee changed manager from 101 to 90 |
101 | 700 | Martin | 18-09-2017 | 18-09-2017 |
|
101 | 710 | Martin | 10-09-2019 | 20-07-2020 |
|
30 | 101 | Sukash | 12-10-2013 | 24-07-2020 | 101 employee changed manager from 50 to 30 |
When I select my RefreshDate based on the emp details hierarchy need to show.
E.g. If I select refresh date 10-09-2015 then 505 emp id should have manager id as 101.
If select 18-09-2017 then then 505 emp id should have manager id as 90
e.g. If I select refresh date 13-12-2013 then 101 emp id should have manager id as 50.
If select 24-07-2020 then then 505 emp id should have manager id as 30
Regards,
Danu
If you select particular Date, anyway you have tagged manager ID to particular employee so it will automatically show respective manager ID. Why do you need hierarchy?
Thanks Kush for your reply. I am sure i missed out some things in my code, below is the code which using.
Step 1: Intially loaded all the data
LOAD *,
(termination_date-hire_date) as TotalEmpDays;
select * from emp where emp_place not in ('ABC','XYZ');
step 2: just loaded with max refresh date and joined
hierarchy(emp_id,manager_id,manager_name) Load
emp_id,manager_id,manager_name;
select emp_id,manager_id,manager_name from emp
where refresh date in (select max(refresh date) from emp) and
where emp_id<> '100010' and status <> 'Exit';
Here becoz of max refresh date am unable to get old emp information when i use hierachy.
When i try to use the pivot table its showing only latest information becoz of joining the max refresh date . I have to fix this. But not getting the exact info for fix.
Regards,
Danu
What is the expected output? Can you not remove the refresh date filter from underlying query?
HI Kush,
This is my expected out put. Here we are showing count of Emp'id against their manager reporting. If manager changed then showing count as 0 at old manager and showing count as 1 at new manager.
Manager ID | 13-12-2013 | 10-09-2015 | 15-10-2016 | 16-10-2016 | 18-09-2017 | 20-07-2020 | 24-07-2020 |
50 | 1 | 1 | 1 | 1 | 1 | 1 | 0 |
101 | 0 | 1 | 1 | 1 | 1 | 2 | 2 |
102 | 0 | 0 | 1 | 1 | 1 | 1 | 1 |
103 | 0 | 0 | 0 | 1 | 1 | 1 | 1 |
90 | 0 | 0 | 0 | 0 | 1 | 1 | 1 |
30 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
for manager 101, should't be count 3 for 24-07-2020?
Hi Kush,
on 24-07-2020, for 101 its 2 only. i.e. 700 and 710 ( already 505 changed)
Thanks,
Danu
try below
Data:
LOAD [Manager ID],
[Emp Id],
[Manager Name],
[Emp Join date],
[Refresh Date] as Refresh_Date
FROM
[lib://Data/Emp.qvd]
(qvd);
Left Join(Data)
LOAD [Emp Id],
count([Emp Id]) as Emp_cnt
Resident Data
Group by [Emp Id];
Left Join(Data)
LOAD date(max(Refresh_Date)) as Max_Refresh_Date;
LOAD FieldValue('Refresh_Date',RecNo()) as Refresh_Date
AutoGenerate FieldValueCount('Refresh_Date');
T1:
LOAD *,
if(Emp_cnt>1,
if([Emp Id]<>Peek([Emp Id]),Refresh_Date,date(peek(Refresh_Date)-1)),Max_Refresh_Date) as Max_Date
Resident Data
order by [Emp Id],Refresh_Date desc;
DROP Table Data;
T2:
LOAD *,
date(Refresh_Date+IterNo()-1) as [Refresh Date]
Resident T1
While Refresh_Date+IterNo()-1<=Max_Date;
Inner Join(T2)
LOAD Distinct Refresh_Date as [Refresh Date]
Resident T1;
DROP Table T1;
Drop Fields Refresh_Date,Max_Refresh_Date,Emp_cnt,Max_Date;
Now you can try creating pivot table with expression as count(distinct EmpID) or count(EmpID)
Thanks Kush for your reply. It looks the solution will work.
I got the below error and looking into that.
Autogenerate: generate count is out of range
LOAD FieldValue('Refresh_Date',RecNo()) as Refresh_Date
AutoGenerate FieldValueCount('Refresh_Date')
Regards,
Danu
hi, looke like field name reference is wrong in your code. change field name. In my example I used 'Refresh_Date' but you need to use field name in your data. If it is [ Refresh Date] then use 'Refresh Date' in fieldvalue and fieldvaluecount function