topic Re: Incremental Load in QlikView

Incremental Load

Mon, 26 Jan 2026 18:19:17 GMT

Hi to all,

I am just try to do incremental reload....actually i just stored my old data in QVD then am trying to reload my new data and Concatenate to my old QVD into my new data...but my old data and new data everthing will be in same place only and i cant reload using max of date because in my new excel old data will be updated so how can i update my new excel file into the old one..please its urgent...thanks in advance to all

Re: Incremental Load

ashfaq_haseeb — Thu, 19 Mar 2015 07:08:46 GMT

Hi,

You need to look at

Insert, Update and Delete methodology.

Have a look at attached document .

Regards

ASHFAQ

Re: Incremental Load

hariprasadqv — Thu, 19 Mar 2015 07:12:32 GMT

Use of Exists and not exists with where condetion will works fine.

Re: Incremental Load

Thu, 19 Mar 2015 07:15:59 GMT

before itself i went through it....but this thing is not will work for me....but that document is helpful...

am looking some other solution..else i want to know how to store a row values in variable using for loop...

Re: Incremental Load

shambhub — Thu, 19 Mar 2015 07:22:48 GMT

Hi Narayan,

Please can you provide table structure from both the sources(QVD & Excel) and please mention Primary key also.

Are you doing incremental load for 1) Insert ,or 2) Insert Update or 3) Insert Update Delete ?

Regards

Shambhu

Re: Incremental Load

Thu, 19 Mar 2015 07:25:26 GMT

Hi,

try this code ..for insert only

Max:

LOAD

Date

FROM

(qvd);

Res:

Load Date as ModDate

Resident Max order by DATIME Desc;

Let MaxTimeStamp=Peek('ModDate',0);

Let MaxTIMESTAMP=Timestamp($(#MaxTimeStamp),'YYYY-MM-DD-hh.mm.ss.ffffff');

Drop Table Res,Max;

//Select from SQL(Database whatever you using)

QV_Table:

SQL SELECT *

FROM ABP WHERE DATIME >= '$(MaxTIMESTAMP)';

CONCATENATE

LOAD

FROM

(qvd) WHERE NOT EXISTS(COMPOKEY);

STORE QV_Table INTO

(qvd);

Drop Table QV_Table;

NOT EXISTS(COMPOKEY) is used for insert and update only.

For insert only don't use this where clause.

Regards

Vimlesh

Re: Incremental Load

its_anandrjs — Thu, 19 Mar 2015 07:29:12 GMT

Hi,

Try Insert Incremental load here that help you. For that see the example load script for that and make changes according to your end

Step One

Final:
LOAD Id,

UpdateTime,
FROM Log_Final.qvd(qvd);

let vMaxTime = Peek('UpdateTime',-1,'Final');
DROP Table Final;

Step Two

Log_Details:
LOAD Id,
UpdateTime
FROM Log_Update.qvd(qvd)

Where UpdateTime > $(vMaxTime);

//Old Data loaded here

Concatenate
LOAD userId,
updateTime
FROM Log_Final.qvd(qvd);

Updated:
NoConcatenate
LOAD userId,
updateTime

Resident Log_Details Order By updateTime asc;
DROP Table Log_Details;
STORE * from Updated into Log_Final.qvd(qvd);

Have a look attached document file for this

Note:- One you have to manually create the Log_Final.qvd first then do incremental load on the new data.

Regards

Anand

Re: Incremental Load

Thu, 19 Mar 2015 07:56:53 GMT

here i attached the snapshot of my data structure....incase i had added one more excel called Transaction-Apr2014.xlsx i need to reload only the Transaction-Apr2014.xlsx...and Concatenate the Qvd file into this Excel file.

Re: Incremental Load

Peter_Cammaert — Thu, 19 Mar 2015 08:33:42 GMT

Do you really want to load historical data into every new Excel file that passes by?

Why not the other way around? Create a QVD (or a resident table in a QVW document) that contains the data from the historical QVD as well as the data from the latest xlsx file?

Re: Incremental Load

Thu, 19 Mar 2015 09:11:48 GMT

order by DATIME Desc......This DATIME is wat is it function or a field...if it is field means let me know clearly.

Re: Incremental Load

Thu, 19 Mar 2015 11:46:53 GMT

hi ,

this is field of your table.

send a set of sample data in excel.

Regards

Vimlesh

Re: Incremental Load

Thu, 19 Mar 2015 12:19:42 GMT

Data is not important i have attached the snap shot of my excel structure....so kindly create like that....

Re: Incremental Load

shambhub — Thu, 19 Mar 2015 13:33:31 GMT

Hi,

Without providing table structure or list of columns it will be difficult to provide solution.

Re: Incremental Load

Fri, 20 Mar 2015 11:02:26 GMT

here is the data Structure....only 4 columns is enough....in the before discussion itself i have mentioned clearly with this image...what i want to get.....Thanks in adavnce

Re: Incremental Load

qepmalheiros — Tue, 20 Oct 2015 19:13:07 GMT

Using QVD Files for Incremental Load

Incremental load is a very common task in relation to data bases. It is defined as loading nothing but new or changed records from the database. All other data should already be available, in one way or another. With QVD Files it is possible to perform incremental load in most cases.

The basic process is described below:

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

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

3. Create a new QVD file.

4. Repeat the procedure for every table loaded.

The complexity of the actual solution depends on the nature of the source database, but the following basic cases can be identified:

1) Case 1: Append Only (typically log files

2) Case 2: Insert Only (No Update or Delete)

3) Case 3: Insert and Update (No Delete)

4) Case 4: Insert, Update and Delete

Below you will find outlined solutions for each of these cases. The reading of QVD files can be done in either optimized mode or standard mode. (The method employed is automatically selected by the QlikView script engine depending on the complexity of the operation.) Optimized mode is (very approximately) about 10x faster than standard mode or about 100x faster than loading the database in the ordinary fashion.

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);

Case 2: Insert Only (No Update or Delete)

If the data resides in a database other than a simple log file the case 1 approach will not work. However, the problem can still be solved with minimum amount of extra work. The following conditions apply:

The data source can be any database.
QlikView loads records inserted in the database after the last script execution.
A field ModificationDate (or similar) is required for QlikView to recognize which records are new.

Script Example:QV_Table:SQL SELECT PrimaryKey, X, Y FROM DB_TABLEWHERE ModificationTime >= #$(LastExecTime)#AND ModificationTime < #$(BeginningThisExecTime)#; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD;STORE QV_Table INTO File.QVD;(The hash signs in the SQL WHERE clause define the beginning and end of a date. Check your database manual for the correct date syntax for your database.)

Case 3: Insert and Update (No Delete)

The next case is applicable when data in previously loaded records may have changed between script executions. The following conditions apply:

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.
This solution will force the reading of the QVD file to standard mode (rather than optimized), which is still considerably faster than loading the entire database.

Script Example:QV_Table:SQL SELECT PrimaryKey, X, Y FROM DB_TABLEWHERE ModificationTime >= #$(LastExecTime)#; Concatenate LOAD PrimaryKey, X, Y FROM File.QVDWHERE NOT Exists(PrimaryKey); STORE QV_Table INTO File.QVD;

Case 4: Insert, Update and Delete

The most difficult case to handle is when records are actually deleted from the source database between script executions. The following conditions apply:

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 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.
This solution will force the reading of the QVD file to standard mode (rather than optimized), which is still considerably faster than loading the entire database.

Script Example:

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

QlikView 11.20 SR6