topic Incremental load mechanisum ? in QlikView

Incremental load mechanisum ?

madhubabum — Wed, 25 Nov 2015 10:04:09 GMT

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.

Re: Incremental load mechanisum ?

swuehl — Wed, 25 Nov 2015 10:38:47 GMT

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.

Re: Incremental load mechanisum ?

madhubabum — Wed, 25 Nov 2015 11:04:01 GMT

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.

Re: Incremental load mechanisum ?

swuehl — Wed, 25 Nov 2015 11:15:57 GMT

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.