Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
LOAD * RESIDENT Sheet1;
drop table Sheet1;
load Key,date(max(Trans_Date),'MM/DD/YYYY') as Trans_DateMax
Resident Raw
group by Key,YearPeriod;
drop field Key;
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
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
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;
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:
Thanks Arthur but look what i got see the attached file.
Hello Thanks for the support i try to apply this but nothing change.
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;
Hello Arthur nothing change same view.