Load Max Date Record for Account with multipel records

[MalcolmCICWF]

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?

[Arthur_Fong]

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]

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]

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]

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