Example data:
| Amount | Transaction Date | CUSTOMER_ACCOUNT_ID |
| 1478.74 | 11/30/16 6:00 | 1 |
| 456 | 11/30/16 8:00 | 1 |
| 154 | 12/1/16 0:00 | 2 |
| 1897.45 | 12/3/16 0:00 | 1 |
| 4538 | 12/3/16 0:00 | 3 |
I want the ONLY the older record from each ID saved in the QVD.
Initially I will have a QVD with all historic, then I want run this everyday and have it add only the customer ID's that dont exist.
PLEASE HELP.
LET vStartDate = MakeDate(2016,12,01);
LET vEndDate = MakeDate(2016,12,03);
FOR vDayNo = vStartDate to vEndDate;
LET vDate = Date(vDayNo, 'MM/DD/YYYY');
Data:
LOAD
Amount as AMOUNT,
"Transaction Date" as TRANSACTION_DATE,
CUSTOMER_ACCOUNT_ID
FROM [lib://AttachedFiles/Test.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE Date("Transaction Date") = Date('$(vDate)') ;
NoConcatenate
Temp1:
LOAD
TRANSACTION_DATE,
CUSTOMER_ACCOUNT_ID,
sum( AMOUNT) AS [AMOUNT]
RESIDENT Data
WHERE AMOUNT <> 0
GROUP BY
TRANSACTION_DATE,CUSTOMER_ACCOUNT_ID;
NoConcatenate
FirstLoadTemp:
LOAD
FirstSortedValue(AMOUNT, (TRANSACTION_DATE)) as Amount,
Date(FLOOR(TRANSACTION_DATE)) as [Transaction Date],
CUSTOMER_ACCOUNT_ID,
CUSTOMER_ACCOUNT_ID2
Resident Temp1
Group by Date(FLOOR(TRANSACTION_DATE)), CUSTOMER_ACCOUNT_ID;
CONCATENATE(FirstLoad)
LOAD *
FROM [lib://folder\Test.qvd](qvd)
WHERE NOT EXISTS (CUSTOMER_ACCOUNT_ID2, CUSTOMER_ACCOUNT_ID);
DROP FIELD CUSTOMER_ACCOUNT_ID2;
STORE FirstLoad INTO [lib://folder\Test.qvd];
DROP TABLE Data;
Drop TABLE FirstLoad;
DROP TABLE Temp1;
NEXT vDayNo
