Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ajaykumar1
Creator III
Creator III

Hierachy in qlik sense

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

Labels (1)
13 Replies
Kushal_Chawda

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?

ajaykumar1
Creator III
Creator III
Author

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

 

 

 

Kushal_Chawda

What is the expected output? Can you not remove the refresh date filter from underlying query?

ajaykumar1
Creator III
Creator III
Author

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 ID13-12-201310-09-201515-10-201616-10-201618-09-201720-07-202024-07-2020
501111110
1010111122
1020011111
1030001111
900000111
300000001
Kushal_Chawda

for manager 101, should't be count 3 for 24-07-2020?

ajaykumar1
Creator III
Creator III
Author

Hi Kush,

on 24-07-2020, for 101 its 2 only. i.e. 700 and 710 ( already 505 changed)

 

Thanks,

Danu

 

Kushal_Chawda

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)

ajaykumar1
Creator III
Creator III
Author

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

Kushal_Chawda

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