Returning latest date by employee

[evie]

I need my script to return the latest date for each EMPLOYEE_ID. I have tried Max(Date#()) and Date(Max(Date#())) and they are not returning what I need them to.

This is what a sample of the raw data looks like:

EMPLOYEE_ID	NAME	SUPERVISOR_ID	SUPERVISOR_NAME	BUSINESS_TITLE	LatestDate
12345	John Smith	21252	Jane Doe	-	12/18/2022
12345	John Smith	21252	Jane Doe	-	1/4/2023
12345	John Smith	21252	Jane Doe	-	3/15/2023
12345	John Smith	21252	Jane Doe	Customer Rep	4/19/2023
21252	Jane Doe	26498	Jason Freedmen	-	3/19/2023
21253	Jane Doe	26499	Jason Freedmen	-	4/30/2023
21253	Jane Doe	26499	Jason Freedmen	Team Lead	5/19/2023

This is what my script is returning:

EMPLOYEE_ID	NAME	SUPERVISOR_ID	SUPERVISOR_NAME	BUSINESS_TITLE	LatestDate
12345	John Smith	21252	Jane Doe	-	4/19/2023
12345	John Smith	21252	Jane Doe	Customer Rep	4/19/2023
12345	John Smith	21252	Jane Doe	-	4/19/2023
12345	John Smith	21252	Jane Doe	-	4/19/2023
21252	Jane Doe	26498	Jason Freedmen	Team Lead	5/19/2023
21253	Jane Doe	26499	Jason Freedmen	-	5/19/2023
21253	Jane Doe	26499	Jason Freedmen	-	5/19/2023

It's replacing all of the dates with the latest date for each employee, which changes the order of the BUSINESS_TITLE.

This is what I need my script to be able to return: 

EMPLOYEE_ID	NAME	SUPERVISOR_ID	SUPERVISOR_NAME	BUSINESS_TITLE	LatestDate
12345	John Smith	21252	Jane Doe	Customer Rep	4/19/2023
21252	Jane Doe	26498	Jason Freedmen	Team Lead	5/19/2023

Just the row with the latest date for all EMPLOYEE_IDs.

How do I do this?

[Kushal_Chawda · Accepted solution]

@evie  try below

Make sure that your Date field is identified as Date if not first you need to convert it to actual date using Date#

Data:
LOAD EMPLOYEE_ID
     NAME
     SUPERVISOR_ID
     SUPERVISOR_NAME
     BUSINESS_TITLE
     LatestDate
FROM Source;

Inner join(Data)
LOAD EMPLOYEE_ID,
     date(max(LatestDate)) as LatestDate
resident Data
group by EMPLOYEE_ID;

[evie]

This also returns the format of that second table where every date for an employee is changed to the most recent date. I don't want it to change them and I would like to only see the row with the latest date.

[Kushal_Chawda]

@evie  Not sure how you are doing it but what I suggested should give you what you are looking for.  I have provided you the script before you are creating second table.  Make sure that field names in group by load matches with previous load so that inner join removes the records which are not tagged to latest date

[BrunPierre]

@evie Here's an alternative solution at the front end using a straight table.

Dimensions: EMPLOYEE_ID, NAME, SUPERVISOR_ID, SUPERVISOR_NAME and BUSINESS_TITLE

Measure (Latest Date): Date(If(Aggr(NODISTINCT Max(LatestDate), EMPLOYEE_ID) = LatestDate, LatestDate))

[MatteoBottichelli]

If you're a developer or programmer, I'm sure there's no problem finding a new job, as these are highly sought-after services these days. But other professions are not lacking in vacancies, which are now conveniently available on the website https://layboard.in/vacancies/jobs-in-uae/jobs-in-dubai/speciality/crane-operator. I like Layboard first of all because there are no fraudulent jobs and unscrupulous companies that cheat employees.