Incremental Load in QlikView

Incremental Load in QlikView Overview

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
Extract the source table in its entirety.
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:

– Append Only

Insert Only

– Insert and Update ( No Delete)

-Insert, Update and Delete

Insert Only: 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).ta 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):

-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

Summary
Review Date
Reviewed Item
Incremental Load in QlikView
Author Rating
51star1star1star1star1star

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Incremental Load in QlikView"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Support


    Please leave a message and we'll get back to you soon.

    3 + 5