Duplicate data but diff. date base on day

[bsbernabe]

Hello There,

My concern is similar in my previews i post but diff. case base on of the day of  date see below  the scenario:

if the customer and serial is same and the date of the Month and Year is same but different Day  how to get the last Agreement ID.

This is a sample data below:

AgreementID	Customer Name	Serial No.	Trans_Date
135844	ROMEO TORRE	2DY1014804	03/06/2015
136622	ROMEO TORRE	2DY1014804	03/07/2015
139602	RIO CURAN	KPY00E077004	04/11/2015
214039	RIO CURAN	KPY00E077004	06/25/2016

Then this is should be:

AgreementID	Customer Name	Serial No.	Trans_Date
136622	ROMEO TORRE	2DY1014804	03/07/2015
139602	RIO CURAN	KPY00E077004	04/11/2015
214039	RIO CURAN	KPY00E077004	06/25/2016

Best Regards,

Bing

[Arthur_Fong · Accepted solution]

Sorry I didnt have QV right now.

Maybe try this:

Try this:

Directory;

Sheet1:
LOAD AgreementID,
[Customer Name],
[Serial No.],
Trans_Date
FROM
[..\..\..\Desktop\Data_.xls]
(biff, embedded labels, table is Sheet1$);

noconcatenate

• Raw:
  load *,[Customer Name]&[Serial No.] as Key,
  [Customer Name]&[Serial No.]&num(Trans_Date) as Key2;
  load *,num#(Year&num(Month,'00')) as YearPeriod;
  load *,Year(Trans_Date) as Year,Month(Trans_Date)as Month;

LOAD * RESIDENT Sheet1;

drop table Sheet1;

• Data:
  NoConcatenate
  load *,Key&num(Trans_DateMax)as Key2;

load Key,date(max(Trans_Date),'MM/DD/YYYY') as Trans_DateMax
Resident Raw
group by Key,YearPeriod;

drop field Key;

[bsbernabe]

Thank you So much...

[bsbernabe]

The solution was so great but i have another concern cause there is a problem the trans_date that suppose to be get the last AgreementID like 

Below is the output right now:  

below is my wish to display if also the Trans date is same get the last AgreementID

Best Regard,

Bing

[Arthur_Fong]

Same concept applied:

Dimension used is AgreementIDMax.

Script:

Directory;

Sheet1:
LOAD
AgreementID,
"Customer Name",
"Serial No.",
Trans_Date
FROM [lib://AttachedFiles/Data_.xls]
(biff, embedded labels, table is Sheet1$);

noconcatenate

Raw:
load *,[Customer Name]&[Serial No.] as Key,
[Customer Name]&[Serial No.]&num(Trans_Date)&AgreementID as Key2;
load *,num#(Year&num(Month,'00')) as YearPeriod;
load *,Year(Trans_Date) as Year,Month(Trans_Date)as Month;
LOAD * RESIDENT Sheet1;

drop table Sheet1;

Data:
NoConcatenate
load *,Key&num(Trans_DateMax)&AgreementIDMax as Key2;
load Key,date(max(Trans_Date),'MM/DD/YYYY') as Trans_DateMax,
max(AgreementID) as AgreementIDMax
Resident Raw
group by Key,YearPeriod;

drop field Key;