Incremental load

Incremental load is basic mechanism used for Optimizing load performance of  the Qlikview applications.

Basic idea is to load data into report in different increments.
Generally in 2 increments-
                                   1. Load newly inserted or updated data from database.
                                   2. Load old data from qvds
Concatenate and generate a new qvd and replace old qvd with new one.

Depending upon the nature of data Incremental loads can be categorized into 3 types.

1. Insert only : New rows will be added into database.
2. Insert and update : New rows , updating  existing rows.
3. Insert Update and delete : New rows , updating and deleting existing rows.

For performing any one of above operations we need ModificationTime field available in database.
And a Primary key is mandatory for performing Update and deletions.

Insert Only:

 

Step1: Filtering newly added records starting from last execution time of the application from database:
 Emp_Table:
SQL SELECT Empid, EmpName, Empage FROM EMP_TABLE
WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(BeginningThisExecTime)#;

Step2: Load old data from qvd and concatenate:
Concatenate
LOAD Empid, EmpName, Empage FROM Emp.QVD;

Step3: Replace old qvd with new one and change Last Execution Time variable:
STORE QV_Table INTO Emp.QVD;
Let LastExecTime = ThisExecTime;

Insert and update:

 

Same as insert except change in Step2.
Where not exists is used to remove duplicate old records.

In first step all records ( newly added and recently updated) will be extracted.

If a record is already in qvd and it got updated it will be extracted in the first step.
This updated record should be deleted from old qvd, for which we use Not exists of Primary key.

Step1: Filtering newly added records starting from last execution time of the application from database:
Emp_Table:
SQL SELECT Empid, EmpName, Empage FROM EMP_TABLE
WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(BeginningThisExecTime)#;

Step2: Load old data from qvd and filter out updated records and concatenate:
LOAD PrimaryKey, X, Y FROM File.QVD
WHERE NOT Exists(PrimaryKey);

Step3: Replace old qvd with new one and change Last Execution Time variable:
STORE QV_Table INTO Emp.QVD;
Let LastExecTime = ThisExecTime;

Insert Update and delete:

 

It is same as Insert and update with an additional inner join statement before Step3.
For Filtering out deleted records Inner join with (existing) Primary key in database is used.

Step1:
QV_Table:
SQL SELECT PrimaryKey, X, Y FROM DB_TABLE
WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(ThisExecTime)#;

Step2:
Concatenate LOAD PrimaryKey, X, Y FROM File.QVD
WHERE NOT EXISTS(PrimaryKey);

Additional Step, Inner join to remove deleted records:
Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

Step4:
STORE QV_Table INTO File.QVD;
Let LastExecTime = ThisExecTime;

                                                                    Hope it helps you :).