Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
MalcolmCICWF
Creator III
Creator III

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

Labels (4)
4 Replies
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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
Creator III
Creator III
Author

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?

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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;

Brett_Bleess
Former Employee
Former Employee

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 do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.