QlikView App Dev

[zakpullen]

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.

Many thanks

[Lisa_P · Accepted solution]

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]

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]

Thank you!