Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
@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 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;
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.
@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
@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))
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.