topic Re: Insert dummy records/fetch latest date in QlikView

Insert dummy records/fetch latest date

MK9885 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I've a requirement where I need to fetch the latest Supervisor for a given Emp.

Below are 2 tables

EMP01 in his period has 3 different supervisors but I only need to show the latest Supervisor (which would be EMP04 for EMP01)

I used different kind of set functions to do this but doesn't exactly works.....

The default count view of a chart should show only the count for last month of every year (Dec, YYYY) and rest other months are invalid for an employee.

@Sunny Talwar

shraddha.g‌

lakshmikandh‌

EMPTABLE:

EMP KEY	EMPLID	NAME	START DT	END DT	ROW
2	1001	EMP01	1/1/2014	1/1/2015	N
7	1001	EMP01	1/2/2015	12/30/2015	Y
3	1001	EMP01	12/31/2015	12/31/9999	N
4	1002	EMP02	1/1/2000	12/31/9999	Y
5	1003	EMP03	1/1/2015	10/30/2015	Y
6	1004	EMP04	12/31/2015	12/31/9999	Y

SUPTABLE:

EMPLID	SUPV ID	START DT	END DT	SUPVNAME
1001	1002	1/1/2014	7/1/2014	EMP02
1001	1003	7/2/2014	12/30/2015	EMP03
1001	1004	12/31/2015	12/31/9999	EMP04

Re: Insert dummy records/fetch latest date

sunny_talwar — Mon, 24 Apr 2017 18:16:40 GMT

May be like this

SUPTABLE:

LOAD EMPLID,

FirstSortedValue([SUPV ID], -[START DT]) as [SUPV ID],

Max([START DT]) as [START DT],

Max([END DT]) as [END DT],

FirstSortedValue(SUPVNAME, -[START DT]) as SUPVNAME

Resident ....

Group By [EMPLID];

Re: Insert dummy records/fetch latest date

MK9885 — Mon, 24 Apr 2017 18:51:00 GMT

Thanks,

In a way I got it but there is actually more to it....

As in the record should be fetched for only Dec and rest all the records should be ignored for that Employee.

It is working with the sample data, I hope it will work with full load as well.....

But thanks a lot .

Re: Insert dummy records/fetch latest date

MK9885 — Mon, 24 Apr 2017 19:09:25 GMT

One more question..

What expression would I need to get only one bar. As only 1 Emp is having active Manager for end date as 12/31/9999

I tried

count({<[END DT] = {'$(=Max([END DT]))'}>} DISTINCT(NAME))

and it gives me bars for 2014 and 2015 as well

[END DT] is new field we got from resident load.

Re: Insert dummy records/fetch latest date

sunny_talwar — Mon, 24 Apr 2017 19:11:05 GMT

May be [END DT] is not formatted as date? Not sure, can you share a sample?

Re: Insert dummy records/fetch latest date

MK9885 — Mon, 24 Apr 2017 19:19:23 GMT

The END DATE is in Date format...

But you can verify

Please find the attachment

Thanks.

Re: Insert dummy records/fetch latest date

sunny_talwar — Mon, 24 Apr 2017 19:23:01 GMT

Here is why you see 2014 and 2015

Re: Insert dummy records/fetch latest date

MK9885 — Mon, 24 Apr 2017 19:37:26 GMT

Yeah but how do I fetch only the latest year (2015) for given Emp and Supervisor?

Cus those 2 dates are for same supervisor but one is his start date in 2014 and other is related to Emp Date