Try the following by ensuring that the date-fields will be interpreted as dates which is depending on your default-settings within the variable DateFormat (at the beginning of your script). If not you need to load the fields as dates, like:
date(date#(VALIDTO, 'DD/MM/YYYY')) as VALIDTO
table:
load VALIDFROM, VALIDTO, WORKER, PERSONNELNUMBER, Status
From YourCSV
where match(Status, 'Working', 'Resigned');
inner join (table)
load date(max(VALIDTO)) as VALIDTO, WORKER, PERSONNELNUMBER
resident table
group by WORKER, PERSONNELNUMBER;
- Marcus
