topic Replace Values with Most Recent date in QlikView

Replace Values with Most Recent date

Kosakamaka — Wed, 24 Feb 2021 21:50:57 GMT

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.

Re: Replace Values with Most Recent date

QFabian — Thu, 25 Feb 2021 03:30:08 GMT

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;

Re: Replace Values with Most Recent date

Kosakamaka — Thu, 25 Feb 2021 14:40:32 GMT

Worked like a charm, thanks a ton!