topic Re: Incremental load code for updation in QlikView

Incremental load code for updation

Thu, 16 Apr 2015 12:12:05 GMT

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

how to implement incremental load for FAct tables

Re: Incremental load code for updation

maxgro — Thu, 16 Apr 2015 18:12:25 GMT

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;

Re: Incremental load code for updation

Thu, 16 Apr 2015 18:17:57 GMT

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

Re: Incremental load code for updation

maxgro — Thu, 16 Apr 2015 18:47:51 GMT

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)

Re: Incremental load code for updation

Sun, 19 Apr 2015 16:14:45 GMT

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

Re: Incremental load code for updation

mukesh24 — Mon, 20 Apr 2015 06:31:07 GMT

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

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

Re: Incremental load code for updation

rwunderlich — Mon, 20 Apr 2015 13:37:12 GMT

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