Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Incremental load code for updation

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

how to implement incremental load for FAct tables

7 Replies
maxgro
MVP
MVP

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;

Not applicable
Author

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
MVP
MVP

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)

Not applicable
Author

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

mukesh24
Partner - Creator III
Partner - Creator III

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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