Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
MalcolmCICWF
Contributor II

Load Max Date Record for Account with multipel records

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?

 

Capture.PNG

4 Replies
Partner
Partner

Re: Load Max Date Record for Account with multipel records

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

MalcolmCICWF
Contributor II

Re: Load Max Date Record for Account with multipel records

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?

Partner
Partner

Re: Load Max Date Record for Account with multipel records

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;

Digital Support
Digital Support

Re: Load Max Date Record for Account with multipel records

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

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.