I am facing problem with Incremental load(insert and update).
I have data initialload.xlsx file with 4 records for initial load and incrementalload.xlsx file contains 4 intial load's updated records and 2 new records. After executing incremental script for first time, when i am displaying QVD content in Table of another app, it shows only 2 new records from incremental.xlsx. After executing incremental script second time, O records is displayed. But After that whenever i execute incremental scripts, it shows proper 6 records , 2 new +4 initial load's data with updated values. This is what actually expected when i execute incremental script first time. I have used modification time for fetching new records and composite key "Voucher Number"&' '&"Account Code" for filtering updated the record. Please suggest some solution to come out from this problem.
InitialLoad Script
InitialLoad:
LOAD
"Account Code",
"Accounting Site Code",
Timestamp(Timestamp#("Approved At",'DD-MMM-YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as "Approved At",
"Credit DC",
"Debit DC",
"Voucher Date (Date)",
"Voucher Number",
"Voucher Number"&' '&"Account Code"
FROM [lib://sampledata/JVInitialLoad.xlsx]
(ooxml, embedded labels, table is intialload);
store InitialLoad into [lib://sampledata/jvqvd1.qvd] (qvd);
drop table InitialLoad;
Exit Script;
IncrementalLoad Script:
FindApproveDate:
LOAD
Max("Approved At")as "ApprovedDate"
FROM [lib://sampledata/jvqvd1.qvd](qvd);
//Let vLastApproved=Timestamp(peek('ApprovedDate',0,'FindApproveDate'));
Let vLastApproved=Timestamp(peek('ApprovedDate',0,'FindApproveDate'));
trace $(vLastApproved);
Drop table FindApproveDate;
NewRecords:
LOAD
"Account Code",
"Accounting Site Code",
Timestamp(Timestamp#("Approved At",'DD-MMM-YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as "Approved At",
"Credit DC",
"Debit DC",
"Voucher Date (Date)",
"Voucher Number",
"Voucher Number"&' '&"Account Code"
FROM [lib://sampledata/JVIncrementalLoad.xlsx]
(ooxml, embedded labels, table is increload)
where Timestamp(Timestamp#("Approved At",'DD-MMM-YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') > '$(vLastApproved)';
Concatenate
LOAD
"Account Code",
"Accounting Site Code",
Timestamp(Timestamp#("Approved At",'DD-MMM-YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as "Approved At",
"Credit DC",
"Debit DC",
"Voucher Date (Date)",
"Voucher Number",
"Voucher Number"&' '&"Account Code"
FROM [lib://sampledata/jvqvd1.qvd] (qvd) where not Exists ("Voucher Number"&' '&"Account Code" );
store NewRecords into [lib://sampledata/jvqvd1.qvd] (qvd);
drop table NewRecords;
exit script;
Second App script:
QVDData:
LOAD
"Account Code",
"Accounting Site Code",
Timestamp(Timestamp#("Approved At",'DD-MMM-YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as "Approved At",
"Credit DC",
"Debit DC",
"Voucher Date (Date)",
"Voucher Number",
"Voucher Number"&' '&"Account Code"
FROM [lib://DataFile/jvqvd1.qvd]
(qvd);
