Insert dummy records/fetch latest date

[MK9885]

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

[sunny_talwar · Accepted solution]

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];

[MK9885]

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 .

[MK9885]

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.

[sunny_talwar]

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

[MK9885]

The END DATE is in Date format...

But you can verify

Please find the attachment

Thanks.

[sunny_talwar]

Here is why you see 2014 and 2015

[MK9885]

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