Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
zakpullen
Contributor III
Contributor III

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
Specialist II
Specialist II

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
Contributor III
Contributor III
Author

Thank you!