Replace Values with Most Recent date

[Kosakamaka]

Hello all, 

Long time lurker here but have finally been put into a situation where I need to reach out for assistance. The current data set that I am working with has has multiple entries for a single ActionID. In general this is not an issue, however, the fact that a single ActionID can have multiple Month Due EV dates creates problems for me. 

What I would like to do is replace the Month Due EV dates for a given ActionID with the most recent date. Is such a thing possible to do during the load script?

Thank you in advance for your assistance. 

[QFabian · Accepted solution]

Hi @Kosakamaka , please check if this works for you :

SET DateFormat='MM/DD/YYYY';

DataX:
LOAD * INLINE [
ActionID, MonthDue
12582, 1/15/2021
12582, 1/15/2021
12582, 1/15/2021
12582, 1/15/2021
12582, 1/15/2021
12582, 3/12/2021
12583, 2/15/2021
12583, 2/15/2021
12583, 2/15/2021
12583, 2/15/2021
12583, 2/15/2021
12583, 4/12/2021
];

left join
Load
ActionID,
date(max(MonthDue)) as MonthDue2
Resident DataX
group By ActionID;

//Optional
drop field MonthDue;
rename field MonthDue2 to MonthDue;

[Kosakamaka]

Worked like a charm, thanks a ton!