topic Re: Duplicate data but diff. date base on day in QlikView

Duplicate data but diff. date base on day

bsbernabe — Sat, 16 Nov 2024 01:54:37 GMT

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

Re: Duplicate data but diff. date base on day

Arthur_Fong — Tue, 19 Nov 2019 10:07:46 GMT

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

Re: Duplicate data but diff. date base on day

bsbernabe — Wed, 20 Nov 2019 07:01:09 GMT

Hello There,

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

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

Re: Duplicate data but diff. date base on day

Arthur_Fong — Wed, 20 Nov 2019 07:11:29 GMT

Try this:

Directory;

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

noconcatenate

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;

Re: Duplicate data but diff. date base on day

MindaugasBacius — Wed, 20 Nov 2019 07:21:08 GMT

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:

Re: Duplicate data but diff. date base on day

bsbernabe — Wed, 20 Nov 2019 07:31:31 GMT

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

Re: Duplicate data but diff. date base on day

bsbernabe — Wed, 20 Nov 2019 07:34:53 GMT

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

Re: Duplicate data but diff. date base on day

Arthur_Fong — Wed, 20 Nov 2019 07:40:34 GMT

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;

Re: Duplicate data but diff. date base on day

bsbernabe — Wed, 20 Nov 2019 08:05:36 GMT

Hello Arthur nothing change same view.

Re: Duplicate data but diff. date base on day

bsbernabe — Wed, 20 Nov 2019 08:17:04 GMT

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

Re: Duplicate data but diff. date base on day

Arthur_Fong — Wed, 20 Nov 2019 08:40:51 GMT

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;

Re: Duplicate data but diff. date base on day

bsbernabe — Wed, 20 Nov 2019 08:56:32 GMT

Thank you So much...

Re: Duplicate data but diff. date base on day

bsbernabe — Wed, 20 Nov 2019 09:45:48 GMT

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

Re: Duplicate data but diff. date base on day

Arthur_Fong — Wed, 20 Nov 2019 10:54:09 GMT

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;