mobileNavlogo
headerlogo

Incremental Load in QlikView with Example

20 September, 2018

Ratings

Related Blogs

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"]

About Author
Authorlogo
Name

TekSlate

Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .