topic Re: Load Max Date Record for Account with multipel records in QlikView

Load Max Date Record for Account with multipel records

MalcolmCICWF — Sat, 16 Nov 2024 01:56:37 GMT

I have found multiple articles, but no solution that I can get to work. I have an account that has multiple records depending on the Date_Filed. I only want the most recent record and the fields that pertain to it. I can achieve this by grouping just the Account # and selecting the Max(Date_Filed). As soon as add in the other fields for the record I am looking for, I cannot get it to work. Can anyone help me out?

Re: Load Max Date Record for Account with multipel records

Arthur_Fong — Fri, 15 Nov 2019 00:03:44 GMT

Try this:

Raw:
load Date_Filed,Date_Granted,Group,ACCTID inline [
Date_Filed,Date_Granted,Group,ACCTID
20170501,20181121,A,11111
20170601,20181210,B,11111
20170701,20190221,A,11111
20170801,20190401,C,11111
];

Data:
LOAD ACCTID, max(Date_Filed) as Date_Filed
resident Raw
GROUP BY ACCTID;

left join(Data)
load * resident Raw;drop table Raw;

Replace Raw with your Excel table.

Thanks and regards,

Arthur Fong

Re: Load Max Date Record for Account with multipel records

MalcolmCICWF — Fri, 15 Nov 2019 20:53:59 GMT

I's actually from a QVD, that's just an example of what the data looks like. I actually have two dates, a created date and a modified date. Is there a way to grab max created date and then the max modified date concurrently?

Re: Load Max Date Record for Account with multipel records

Arthur_Fong — Mon, 18 Nov 2019 07:58:09 GMT

Try this:

Data:

Load ACCTID, max(Date_Filed) as Date_Filed, max(Modified_Date) as Modified_Date

resident Raw;

Group by ACCTID;

left join(Data)

load * resident Raw;

drop table Raw;

Re: Load Max Date Record for Account with multipel records

Brett_Bleess — Tue, 19 Nov 2019 15:34:47 GMT

Jeremy, did Arthur's last post get you what you needed to get things working as you wanted? If so, do not forget to return to the thread and use the Accept as Solution button on that post to give Arthur credit for the help and to let other Community Members know it actually worked too. If you are still working on things, please leave an update for us.

Regards,
Brett