Incremental Load in QlikView with Example

Incremental Load in QlikView

An incremental load is used to transfer data from one database to another efficiently and avoid the unnecessary use of resources. There are two ways to maintain the database up to date. They are

1. Extract the source table in its entirety.

2. Extract only the new and/or modified transactions from the source table and append those records to the ones we previously saved in our Base QVDs.

The second option is what we call an Incremental Load.

The basic process is described below:

- Load the new data from Database table (a slow process, but loading a limited number of records).

- Load the old data from QVD file (loading many records, but a much faster process).

- Create a new QVD file.

- Repeat the procedure for every table loaded.

Different types in Incremental Load Process

1. Append Only

2. Insert Only

3. Insert and Update ( No Delete)

4. Insert, Update and Delete

Insert Only - Example

The database must be a log file (or some other file in which records are appended and not inserted or deleted) which is contained in a text file (no ODBC/OLE DB).QlikView keeps track of the number of records that have been previously read and loads only records added at the end of the file.

Ex: Buffer (Incremental) Load * From LogFile.txt

Insert Only (No Update or Delete) - Example

The data source can be any database.

QlikView loads records inserted in the database after the last script execution. A field Modification Date (or similar) is required for QlikView to recognize which records are new.

Ex: QV_Table: SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)# AND ModificationTime < #$(BeginningThisExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD;

STORE QV_Table INTO File.QVD;

Insert and Update (No Delete)

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.

Eg: 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;

Insert, Update and Delete

The data source can be any database. QlikView loads records inserted into the database or updated in the database after the last script execution. QlikView removes records deleted from the database after the last script execution. A field Modification Date (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.

Ex: Let ThisExecTime = Now( );

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)# AND ModificationTime < #$(ThisExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT EXISTS(PrimaryKey);

Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

If ScriptErrorCount = 0 then STORE QV_Table INTO File.QVD;

Let LastExecTime = ThisExecTime;

End If

[wpob id="1"]