picking max of date in where clause

[G3S]

hi,

data is something like below.

POL	Carrier	Version	RatesValidity	TEU
Naples	AAA	2020	30/06/2020	15
Naples	AAA	2020	31/12/2020	15

same Version can have multiple RatesValidity. 

need  TEU of the latest Ratevalidity as the result (second line in above table)

in the script,  in where clause, unable to use expression :

where max(RatesValidity)

Help please?

[Taoufiq_Zarra]

Hi,

One solution :

Data:
LOAD * INLINE [
    POL, Carrier, Version, RatesValidity, TEU
    Naples, AAA, 2020, 30/06/2020, 15
    Naples, AAA, 2020, 31/12/2020, 15
];

output:
noconcatenate
load   POL, Carrier, Version, Date(Max(Date#(RatesValidity,'DD/MM/YYYY'))) as RatesValidity, FirstSortedValue(TEU,-Date#(RatesValidity,'DD/MM/YYYY')) as TEU resident Data group by POL, Carrier, Version;

drop table Data;

output :

[G3S]

@Taoufiq_Zarra  Thank you.

If there are other fields in the 'Data' table should they be loaded in the 'output' table? 
in my question I only mentioned the fields relevant to my query.

[arulsettu]

You can try this as well

TempDate:

Load Max(RatesValidity) as Maxdate from table;

Let vmax = peek('Maxdate',0,'TempDate');

drop table TempDate;

// Now use this variable in your load statement where clause

load

POL,

Carrier,

Version,

RatesValidity,

TEU

from table_name where RatesValidity >='$(vmax)';

*Include the fields you want

[Taoufiq_Zarra]

@G3S 

YES