Re: Incremental load code for updation - Qlik Community

Report Inappropriate Content · ‎2015-04-16

Hi can any body suggest how to implement incremental load when updation is there and

how to implement incremental load for FAct tables

maxgro · ‎2015-04-16

from QlikView help (look for using qvd files for incremental log)

Case 3: Insert and Update (No Delete)

The next case is applicable when data in previously loaded records may have changed between script executions. The following conditions apply:

The data source can be any database.
QlikView loads records inserted into the database or updated in the database after the last script execution
A field ModificationDate (or similar) is required for QlikView to recognize which records are new.
A primary key field is required for QlikView to sort out updated records from the QVD file.
This solution will force the reading of the QVD file to standard mode (rather than optimized), which is still considerably faster than loading the entire database.

Script Example:

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT Exists(PrimaryKey);

STORE QV_Table INTO File.QVD;

Report Inappropriate Content · ‎2015-04-16

Hi

Massimo,

if supposed to be single record has same primary key and values are updated.how to update the values.

i mean updated records are on existing primary key.

And let me know how to proceed for facts there wont be no pk.????

regards

maxgro · ‎2015-04-16

if supposed to be single record has same primary key and values are updated.how to update the values.

i mean updated records are on existing primary key.

you need a modification date or something like that on the database to identify the changed records

load the modified record from the db

don't load the record from qvd (where not exists ...)

And let me know how to proceed for facts there wont be no pk.????

don't know; if a modified db record is already in your qvd you load it twice no pk to discard from qvd)

Report Inappropriate Content · ‎2015-04-19

can any body help me how to implement incremental load for fact tables.

mukesh24 · ‎2015-04-20

Hii Pallasri,

For example : Sales as Fact Table. Field as SalesRep, SalesEntryDate,SalesQty.

SalesRep is Primary Key.

To load delta -->

Let vYesterday = date(Today()-1);

A:

Select * From Sales where SalesEntryDate = $(vYesterday);

Append or update data into history data :-

Sales:

Load * Resident A;

concatenate

Load *

From Sales where not exists(SalesRep);

Store Sales into '...\Sales.qvd';

rwunderlich · ‎2015-04-20

If you don't have a unique key, then you need to update all replace all records that have the key. To do that, you'll need to make a copy of the updated key. For example, say your (non-unique) key is OrderDate:

Updates:

SELECT X, Y, OrderDate FROM table where....;

TempKey:

LOAD OrderDate as OrderDate2 RESIDENT Updates;

CONCATENATE (Updates) LOAD * FROM Orders.qvd (qvd)

WHERE NOT Exists(OrderDate2,OrderDate);

I've posted an incremental reload script template here:Qlikview Cookbook: Delta Load Template http://qlikviewcookbook.com/recipes/download-info/delta-load-template/

You'll have to adjust it for the non-unique key scenario.

-Rob