topic Re: Hierachy in qlik sense in App Development

Hierachy in qlik sense

ajaykumar1 — Sat, 16 Nov 2024 01:48:35 GMT

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

Re: Hierachy in qlik sense

Kushal_Chawda — Fri, 24 Jul 2020 15:57:19 GMT

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?

Re: Hierachy in qlik sense

ajaykumar1 — Fri, 24 Jul 2020 18:49:12 GMT

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

Re: Hierachy in qlik sense

Kushal_Chawda — Fri, 24 Jul 2020 21:09:48 GMT

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

Re: Hierachy in qlik sense

ajaykumar1 — Sun, 26 Jul 2020 15:27:30 GMT

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

Re: Hierachy in qlik sense

Kushal_Chawda — Mon, 27 Jul 2020 20:23:54 GMT

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

Re: Hierachy in qlik sense

ajaykumar1 — Tue, 28 Jul 2020 07:05:45 GMT

Hi Kush,

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

Thanks,

Danu

Re: Hierachy in qlik sense

Kushal_Chawda — Tue, 28 Jul 2020 15:37:31 GMT

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)

Re: Hierachy in qlik sense

ajaykumar1 — Wed, 29 Jul 2020 11:16:51 GMT

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

Re: Hierachy in qlik sense

Kushal_Chawda — Wed, 29 Jul 2020 11:22:08 GMT

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

Re: Hierachy in qlik sense

ajaykumar1 — Wed, 29 Jul 2020 13:53:51 GMT

Hi Kush,

I have applied my field name as you mentioned then getting the 0 records. Is it possible to share your app?

Also do we no need to use Hierarchy again? Earlier i used hierarchy to drill down and see the lowest level granularity.

Regards,

Danu

Re: Hierachy in qlik sense

Kushal_Chawda — Wed, 29 Jul 2020 14:02:20 GMT

I don't see any use of hierarchy function here as it is more about dependent on refresh date. Please see the attached app.

Re: Hierachy in qlik sense

ajaykumar1 — Wed, 29 Jul 2020 17:10:05 GMT

Thanks Kush, but small gap is there.

e.g. for emp id 101 the manager id is 50 upto 20-07-2020 ( where manager id changed to 30 on 24-07-2020).

But we are getting only 1 on 13/12/2013. we have to get upto 20-07-2020.

Thank you so much for your help.

Re: Hierachy in qlik sense

Kushal_Chawda — Wed, 29 Jul 2020 17:18:44 GMT

actually I had added some dummy data to test on multiple manager changes. That is why count will be different. You can try on your data and let me know if any gaps