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]

Try this:

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

load AgreementID,[Customer Name],[Serial No.],Date(Date#(Trans_Date,'MM/DD/YYYY'),'MM/DD/YYYY')as Trans_Date inline [
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];

NoConcatenate
Data:
load *,Key&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;

In front end table, drag in the dimensions as shown, use Trans_DateMax instead of Trans_Date.

Exclude null value in the table.

Thanks and regards,

Arthur Fong

[bsbernabe]

Hello There,

I already try to apply your script but i encounter some error Kindly see the attached sample data.

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

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

Data:
NoConcatenate
load *,Key&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;

Thanks,

Bing

[Arthur_Fong]

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.]&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&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;

[MindaugasBacius]

 I could think of this:

tmp:
LOAD Max(AgreementID) as AgreementID,
Year(Trans_Date) & '-' & Month(Trans_Date) as [Year-Month],
Date(Max(Trans_Date)) as Trans_Date
FROM
[https://community.qlik.com/t5/New-to-QlikView/Duplicate-data-but-diff-date-base-on-day/td-p/1648358]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1)
Group by Year(Trans_Date) & '-' & Month(Trans_Date)
;

DROP Field [Year-Month];

Left join(tmp)
LOAD AgreementID,
[Customer Name],
[Serial No.],
Trans_Date
FROM
[https://community.qlik.com/t5/New-to-QlikView/Duplicate-data-but-diff-date-base-on-day/td-p/1648358]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

The result is the same as you wish:

[bsbernabe]

Thanks Arthur but look what i got see the attached file.

[bsbernabe]

Hello Thanks for the support i try to apply this but nothing change.

[Arthur_Fong]

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.]&Trans_Date as Key2;
load *,num#(Year&num(Month)) as YearPeriod;
load *,Year(Trans_Date) as Year,Month(Trans_Date)as Month;

LOAD * RESIDENT Sheet1;

drop table Sheet1;

Data:
NoConcatenate
load *,Key&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]

Hello Arthur nothing change same view.

[bsbernabe]

Hi, Thanks for this but nothing changes can you send me qvw. can you use
the excel attached file for the script you send.?