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;
Thank you So much...
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
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;