Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
madhubabum
Creator
Creator

Incremental load mechanisum ?

Hi Experts

I have the following Scenario,

I am created variable

let Last_Modified_Date=Peek('MaxDate',0,'Last_Modified_Date');

Incremental:

LOAD ProductID, ProductName, Sales,  ModifiedDate

FROM IncrementSales.xlsx (ooxml, embedded labels, table is Sheet1) Where ModifiedDate>'$(Last_Modified_Date)';

concatenate

LOAD ProductID, ProductName,  Sales,  ModifiedDate

FROM [QVD\SalesMain.qvd(qvd)] (qvd) where not Exists(ProductID);

store Incremental into 'F:SalesMain.qvd(qvd)';

DROP Table Incremental;

Note : My incremental load is Working Fine ..

            But How Concatenate & Where not Exists working for my script, with my under standing highlighted part only inserts new records, but it is updating the records as well . can anyone explain how modification is happening on records.

           

3 Replies
swuehl
MVP
MVP

You are first loading records with modification date higher than the previously loaded modification date. In this LOAD, it will already load new or updated product records, it just looks at modification date.

Then, your highlighted part loads in all product records with a ProductID not already loaded in the first load, i.e. unchanged / old data.

madhubabum
Creator
Creator
Author

Thanks for the reply Swuehl, Actually in my case that IncrementalSales.xls consists last 48 hours data only so let us assume that we have ignored modified date concept for a while. Even if i removed the modified date where clause from the script, modification and insertion of records happening . i am unable to find the mechanism in highlighted part. Can you explain me this.

Regards,

Madhu. 

swuehl
MVP
MVP

Not sure if I can explain it much better than in my previous post.

Your current script will not check if IncrementalSales.xls shows updated or new records, it just checks for Mod date (or, if you ignore Mod date, just brings in all records). But it will not check if the Product ID already exists in your historic QVD  at this time, hence the records could bring in new Product ID  information, or updated (in a sense that the Product ID already exists in the QVD), that's just how we could describe the information when knowing both files, or we are just saying, the IncrementalSales.xls might bring in New OR Updated.

Right?

When you then concatenate your historic data, you only do this for records with Product ID that are not New OR Updated (WHERE NOT EXISTS(ProductID) ), i.e. old AND unchanged data.