Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bsbernabe
Creator
Creator

Duplicate data but diff. date base on day

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:

AgreementIDCustomer NameSerial No.Trans_Date
135844ROMEO TORRE2DY101480403/06/2015
136622ROMEO TORRE2DY101480403/07/2015
139602RIO CURAN KPY00E07700404/11/2015
214039RIO CURAN KPY00E07700406/25/2016

 

Then this is should be:

AgreementIDCustomer NameSerial No.Trans_Date
136622ROMEO TORRE2DY101480403/07/2015
139602RIO CURAN KPY00E07700404/11/2015
214039RIO CURAN KPY00E07700406/25/2016

 

Best Regards,

Bing

Labels (1)
1 Solution

Accepted Solutions
Arthur_Fong
Partner
Partner

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;

View solution in original post

13 Replies
Arthur_Fong
Partner
Partner

Try this:

MC.PNG

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

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
Partner
Partner

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
Partner
Partner

 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:

Screenshot_1.png

bsbernabe
Creator
Creator
Author

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

 

bsbernabe
Creator
Creator
Author

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

 
Arthur_Fong
Partner
Partner

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

Hello Arthur nothing change same view.

bsbernabe
Creator
Creator
Author

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