How to load record with the highest TimeStamp

[QS_]

I'm loading some flight data that is being used for further calculations. However, some flights have multiple records , like in the table below.

Flight Number	flt_date	Dep	Arr	fob_ontime_kg	zfw	LatestTimeStamp
777	2023-10-01	ABC	XYZ	7257.48	197561	10/1/2023 8:46:00 AM
777	2023-10-01	ABC	XYZ	7257.48	197630	10/1/2023 8:23:00 AM
777	2023-10-01	ABC	XYZ	7257.48	197649	10/1/2023 8:27:00 AM

I want to load only the record with the latest timestamp. I'm using the code below. But this does not work; all records are being loaded regardless of the max time.

Temp:

LOAD 

Date(flt_date_local,'DD/MM/YYYY')&' '&Num(flt_no)&' '&dep_stn&' '&arr_stn as UNQ_local,

    message_created_datetime_local,

    zfw;

Select

flt_date,

    flt_no,

    dep_stn,

    arr_stn,

    message_created_datetime_local,

    zfw

from detail_fact_v WHERE status='FINAL' and flt_date BETWEEN to_date('$(vStartDate)','DD/MM/YYYY') AND to_date('$(vEndDate)','DD/MM/YYYY');

Loadsheet:

Load 

FirstSortedValue(UNQ_local,message_created_datetime_local) as UNQ_local,

Timestamp(max(message_created_datetime_local)) as LatestTimeStamp,

FirstSortedValue(zfw, -message_created_datetime_local) as zfw

Resident temp

Group By UNQ_local, zfw;

drop table temp;

[PrashantSangle]

Hi,

try below

Temp:

LOAD 

Date(flt_date_local,'DD/MM/YYYY')&' '&Num(flt_no)&' '&dep_stn&' '&arr_stn as UNQ_local,

    message_created_datetime_local,

    zfw;

Select

flt_date,

    flt_no,

    dep_stn,

    arr_stn,

    message_created_datetime_local,

    zfw

from detail_fact_v WHERE status='FINAL' and flt_date BETWEEN to_date('$(vStartDate)','DD/MM/YYYY') AND to_date('$(vEndDate)','DD/MM/YYYY');

NoConcatenate

flight_no_with_max_date:

Load flt_no&'_'&max_flt_date as key;

Load flt_no, max(flt_date) as max_flt_date Resident Temp;

NoConcatenate

Final:

Load * Resident Temp

where exist(key,flt_no&'_'&flt_date);

drop table Temp, flight_no_with_max_date;

Regards,

Prashant Sangle

[marcus_sommer]

zfw is included within the group by and prevent the intended aggregation.