topic Re: Incremental load in QlikView

Incremental load

hari8088 — Mon, 13 Jul 2015 01:03:44 GMT

Hi,

In incremental load process how the system will understand which one basic load and which one is incremental load

Re: Incremental load

Mon, 13 Jul 2015 01:29:48 GMT

Hi,

System is not going to understand and differentiate Full or Incremental Load.

You should handle this in your code (Script).

IF else condition while scripting Incremental Load

Regarding loading DIM data and FACT data in QVD file, I got some doubt:

Three Types of Qlikview Incremental Loads

Large transactional tables can be significantly time consuming in a reload. Taking advantage of Qlikview’s incremental load logic can reduce that time considerably. An incremental load is a process of simply appending new records or updating specific records in an existing QVD. There are three key scripting options available for incremental loads.

Insert Only
Insert & Update
Insert, Update, & Delete

SET UP

Each of these three scenarios is designed to run once an INITIAL LOAD has occurred. An initial load is a task that creates the source QVDs. These QVDs from then on can be optimized to reload with one of the following incremental load scripts. Since an incremental load is designed to pull only new or altered data, a source QVD is needed to hold all non-modified information and must exist before an incremental load can run.

INSERT ONLY

For an INSERT ONLY scenario, there is the assumption that new data will not create duplicate records. There is no set way to determine NEW data, so this must be reviewed case by case. Once a method for finding new records is determined, the reload process is a simple three step process.

Load all NEW data from the data source
Concatenate this data with a load of all data from the QVD file
Store the entire table out to the QVD file

As long as the QVD is named the same, this will overwrite the previous QVD so the process can repeat for the next reload.

INSERT & UPDATE

The INSERT & UPDATE scenario also takes new data from the source but it also pulls in updated records. Additional precautions need to be taken in order to avoid duplicate records. During the load from the QVD, exclude records where there is a match on the primary key. This will ensure that the updated records will not be duplicated.

Load all NEW and UPDATED data from the data source
Concatenate this data with a load of only the missing records from the QVD file
Store the entire table out to the QVD file

Example of ScriptData:
SQL SELECT
           PrimaryKey,
           A,
           B,
           C
FROM DB_Table
WHERE ModifyDate >= $(vDate);

CONCATENATELOAD
           PrimaryKey,
           A,
           B,
           C
FROM Data.qvd
WHERE NOT exists (PrimaryKey);

STORE Data into Data.qvd;Using the Exists() function keeps the QVD from loading the obsolete records since the UPDATED version is currently in memory.

INSERT, UPDATE, & DELETE

An INSERT, UPDATE, & DELETE script is very similar to the load process of the INSERT & UPDATE, however there is an additional step needed to remove deleted records. The most effective method is to load all the PrimaryKeys from the source and then apply an inner join. This will achieve the delete process.

Load all NEW and UPDATED data from the data source
Concatenate this data with a load of only the missing records from the QVD file
Inner join all PrimaryKeys from the data source
Store the entire table out to the QVD file

Example of Script

Data:
SQL SELECT
PrimaryKey,
A,
B,
C
FROM DB_Table
WHERE ModifyDate >= $(vDate);

CONCATENATE

LOAD
PrimaryKey,
A,
B,
C
FROM Data.qvd
WHERE NOT exists (PrimaryKey);

INNER JOIN

SQL SELECT
PrimaryKey,
FROM DB_Table;

STORE Data into Data.qvd;

Very large data sets can take a long time to load and greatly effect the performance of your QlikView documents over time. By implementing QVD optimization with incremental loads, this technique can be employed to perform faster loads in less time, utilizing less system resources.

Re: Incremental load

settu_periasamy — Mon, 13 Jul 2015 01:58:05 GMT

Hi,

i don't think, there is any special function to identify the incremental process. Incremental load is a mechanism by which data is appended, updated or deleted in large tables using the where condition

Re: Incremental load

jagan — Mon, 13 Jul 2015 02:30:26 GMT

Hi,

If you are using Case 1: Append Only (used for Log files) then qlikview will know that if it is basic or Incremental loading by identifying the Buffer Incremental keywords in the statement. For other scenarios Qlikview will not know whether it is Normal or Incremental load.

Case 1: Append Only

The simplest case is the one of log files; files in which records are only appended and never deleted. The following conditions apply:

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.

Script Example:

Buffer (Incremental) Load * From LogFile.txt (ansi, txt, delimiter is '\t', embedded labels);

Buffer Load:

The syntax is:

buffer[ (option [ , option] ) ] ( loadstatement | selectstatement )

where:

option ::= incremental | expiry

expiry::= stale [after]amount[ (days | hours)]

amount is a number specifying the time period. Decimals may be used. The unit is assumed to be days if omitted.

The incremental option enables the ability to read only part of an underlying file. Previous size of the file is stored in the XML header in the QVD file. This is particularly useful with log files. All records loaded at a previous occasion are read from the QVD file whereas the following new records are read from the original source and finally an updated QVD-file is created. Note that the incremental option can only be used with load statements and text files and that incremental load cannot be used where old data is changed or deleted!

Re: Incremental load

maxgro — Mon, 13 Jul 2015 05:20:33 GMT

you can use some method in the script to figure out which case (incremental / basic)

some examples

- usually in incremental load you store data in QVD files; the first time the QVD are missing then you're in basic; second time, third, etc,,, the files are present so you're in the incremental case

- other possibility is to store in an external file the information (a date, a key) to be used for incremental; when you find the date or key in the file you're doing an incremental, otherwise you're in the basic case

Re: Incremental load

krishna20 — Mon, 13 Jul 2015 07:52:19 GMT

Hi,

Please find below attached file.It may helps.