topic Re: Incremental Load in QlikView

Incremental Load

alec1982 — Mon, 21 Sep 2015 05:55:51 GMT

hi all,

Trying to find the quickest way to do incremental load from a set of xml files.. the files have the names include date string such as 2015-09-16 and so on. once the file is received and stored into the directory, it will never change and we receive a new file every night..

sample of files names:

2015-09-10-Data.xml

2015-09-11-Data.xml

2015-09-12-Data.xml

I thought about storing the data into qvd then load next file but not sure how to do that..

any idea?

Thxs,

Alec

Re: Incremental Load

Anonymous — Mon, 21 Sep 2015 06:39:40 GMT

Alec,

In your case, as you said you are getting data on every night..

Just making sure, are you getting old data as well every time or its only updated data?

Like today is 21st Sept, so in today night file will you be having 18th Sep or 19 Sept or 20 Sept data?

or it will be of 21st sept only?

Re: Incremental Load

tresB — Mon, 21 Sep 2015 06:42:06 GMT

May be as simple as below code:

Store the first xml file into qvd, like:

FirstTable:

Load

From <FirstXml>;

Store FrstTable into ....Data.qvd(qvd);

Drop table FirstTable;

Final:

Load

From ....Data.qvd(qvd);

Concatenate // not needed if all fields are common (which is the case in general)

Load

From ...<DailyNew XML>; // this could be automated(the name has not be hard coded) if you keep the new file in a separate folder or take in same name and overwrite

Store Final into .....Data.qvd(qvd);

Re: Incremental Load

jonathandienst — Mon, 21 Sep 2015 06:42:28 GMT

I assume that you have a load statement that can load the XML files.

Then use something like this

// Get the last loaded date

T_MaxDate:

Load Num(Max(EffDate)) As MaxDate

From Store.qvd (qvd);

Let vMaxDate = Peek('MaxDate');

DROP Table T_MaxDate;

// Load historical data from storage qvd

Data:

LOAD *

From Store.qvd (qvd);

// Now loop over the source files to find those not yet loaded

For Each vFile in FileList('<path to qvd files>\*.xml')

Let vBasename = SubField(SubField(vFile, '\', -1), ',', 1);

Let vFileDate = Num(Date#(Left(vBaseName, 10), 'YYYY-MM-DD'));

If vFileDate > vMaxDate Then

Concatenate(Data)

LOAD *,

Date(vFileDate) As EffDate

From [$(vFile )] (......);

End If

Next

// Update the storage qvd

STORE Data Into Store.qvd (qvd);

Modify the storage qvd file name/path and FileList path and XML qualifiers to suit your environment.

Re: Incremental Load

alec1982 — Mon, 21 Sep 2015 06:55:05 GMT

I will have two issues here..

The load statement result in a single table but it has two joins.

the xml files have 14 tables each and I am loading three of them only such as

Table1:

Load A

from xml file table xyz;

Join (Table1)

Load A,

from xml file table ABC;

Join (Table1)

Load A,

from xml file table SS;

Store Table1 into AA.QVD;

also , we receive the files everyday at 8 pm and it has today's date.

finally, how do I handle the old files if I want to include them?

Re: Incremental Load

Anonymous — Mon, 21 Sep 2015 07:06:59 GMT

Alec,

Their is no logic if you are having today date's data only in a file beacuse you may required old data as well and on daily basis you need to concatenate the new updated file...

Re: Incremental Load

alec1982 — Mon, 21 Sep 2015 07:35:45 GMT

Jonathan,

while having total 5 files and the first one is on 2015-09-16 and it is already loaded to create the initial qvd, I am having some issues with the variables.

the other files are for the dates 17, 18, 19, 20 of September.

VFile shows only one value = C:\2015-09-20-Data.xml.

vBaseName returns 2015-09-20-Data.xml

vFileDate is not created at all.

Any idea what I am doing wrong?

Re: Incremental Load

alec1982 — Mon, 21 Sep 2015 08:13:07 GMT

I found the issue with the variable creation. it is minor naming issue...

the only thing I need help with now is how to handle the join

Table1:

Load A

from xml file table xyz;

Join (Table1)

Load A,

from xml file table ABC;

Join (Table1)

Load A,

from xml file table SS;

Re: Incremental Load

jonathandienst — Mon, 21 Sep 2015 11:09:29 GMT

Basename / BaseName -- oops

You can't do a join inside the loop. The join adds data fields and after the first pass the fields exist and they become part of the join key. What you need to do is collect each part in a separate table and join them after the loop. You will also need to concatenate the storage QVD after the join for the same reason.

Like this:

// Get the last loaded date

T_MaxDate:

Load Num(Max(EffDate)) As MaxDate

From Store.qvd (qvd);

Let vMaxDate = Peek('MaxDate');

DROP Table T_MaxDate;

// Now loop over the source files to find those not yet loaded

For Each vFile in FileList('<path to qvd files>\*.xml')

Let vBasename = SubField(SubField(vFile, '\', -1), ',', 1);

Let vFileDate = Num(Date#(Left(vBasename, 10), 'YYYY-MM-DD'));

If vFileDate > vMaxDate Then

Data:

LOAD *,

Date(vFileDate) As EffDate

From [$(vFile )] (......table is 'xyz');

Data2:

LOAD *,

Date(vFileDate) As EffDate

From [$(vFile )] (......table is 'abc');

Data3:

LOAD *,

Date(vFileDate) As EffDate

From [$(vFile )] (......table is 'def');

End If

Next

Left join (Data)

LOAD * Resident Data2;

Left join (Data)

LOAD * Resident Data3;

Concatenate(Data)

LOAD *

From Store.qvd (qvd);

STORE Data Into Store.qvd (qvd);

Re: Incremental Load

jonathandienst — Mon, 21 Sep 2015 11:11:04 GMT

And this script will load any newer not-yet-loaded files, even if the script does not run for a few days for some reason.

Re: Incremental Load

qepmalheiros — Tue, 20 Oct 2015 19:11:35 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