topic Re: How do I return latest date by employee as new script field or by filtering out older dates? in QlikView

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

zakpullen — Mon, 05 Sep 2022 11:23:37 GMT

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

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

Lisa_P — Tue, 06 Sep 2022 00:22:15 GMT

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];

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

BrunPierre — Tue, 06 Sep 2022 03:40:55 GMT

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;

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

zakpullen — Thu, 08 Sep 2022 08:54:15 GMT

Thank you!