Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ronak21
Contributor
Contributor

Problem in Incremental Load(Insert and Update)

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);

0 Replies