Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zakpullen
Creator
Creator

How do I return latest date by employee as new script field or by filtering out older dates?

Hi,

I'm loading employee data from an excel file (about 20 fields). One field displays review dates. For some employees there is more than one record. How do I return only the latest review date for them, or stop unrequired, older dates (for employees with more than one date) from being loaded at all? I thought firstsortedvalue might be the solution, but I can't get it to work.

In the example below, I want only the later date loaded into the script, or it can be returned in a  new field.

reviewdate.JPG

Many thanks

 

Labels (2)
1 Solution

Accepted Solutions
Lisa_P
Employee
Employee

You can do this by creating an aggregation table to aggregate [Review Date] by [Employee ID] ..

Load [Employee ID],
           Max([Review Date]) as [Latest Review Date]
Resident TableName
group by [Employee ID];

View solution in original post

3 Replies
Lisa_P
Employee
Employee

You can do this by creating an aggregation table to aggregate [Review Date] by [Employee ID] ..

Load [Employee ID],
           Max([Review Date]) as [Latest Review Date]
Resident TableName
group by [Employee ID];

BrunPierre
Master
Master

Possibly this using FirstSortedValue

 

TEmployeeTable:
LOAD *Inline[
Employee ID,Name,Review Date
1234,Jo bloggs,01/04/2020
1234,Jo bloggs,31/03/2022
];

NoConcatenate
EmployeeTable:
LOAD Distinct [Employee ID], 
Name,
FirstSortedValue([Review Date],-[Review Date]) as [Review Date]
Resident TEmployeeTable
Group By [Employee ID],Name
Order By [Employee ID];

DROP Table TEmployeeTable;

 

 

zakpullen
Creator
Creator
Author

Thank you!