topic Re: Incremental Load without Updated Date and Time in QlikView

Incremental Load without Updated Date and Time

Anonymous — Wed, 09 Sep 2015 06:40:05 GMT

The file I have for the Initial load is Sample.xlsx

Number	Alphabet
1	A
2	B
3	C
4	D

After loading this file, I'm storing it as a QVD File using the following Code,

Sample:

LOAD Number,

Alphabet

FROM Sample.xlsx (ooxml, embedded labels, table is Sheet1);

Store Sample into Sample.qvd(qvd);

I have inserted a new row to the Sample.xlsx as

Number	Alphabet
1	A
2	B
3	C
4	D
5	E

For performing Incremental Load (Insert and Update) on this data, I have added the following code to the existing Code.

MaxNumber:

LOAD max(Number) as MaxNumber

Resident Sample;

Let vmax=peek('MaxNumber',0,'Last_Updated_Number');

Drop Table MaxNumber;

IncTable:

LOAD Number,

Alphabet

FROM Sample.xlsx

(ooxml, embedded labels, table is Sheet1) where Number > $(vmax);

Concatenate

LOAD Number,

Alphabet

FROM Sample.qvd (qvd) where not Exists(Number);

Drop Table IncTable;

Once I reload this, I'm encountering a problem like

What is even more baffling is, I got the updated data when I checked the Preview of the Table in Data model.

Can someone help me on this? Where I was wrong and What needs to be done to get this without any Script Error?

Thanks

Re: Incremental Load without Updated Date and Time

Ralf-Narfeldt — Wed, 09 Sep 2015 06:49:11 GMT

You are still loading the Sample: table at the start, right?

Then the load in IncTable automatically concatenates, and as IncTable is not created you can't drop it.

Re: Incremental Load without Updated Date and Time

tresB — Wed, 09 Sep 2015 06:51:28 GMT

Drop sample table before IncTable, like:

......

Drop Table Sample;

IncTable:

LOAD Number,

Alphabet

....

Re: Incremental Load without Updated Date and Time

Anonymous — Wed, 09 Sep 2015 06:59:28 GMT

Thanks for the help. If I drop the Sample Table, I'm not getting any table loaded into Qlikview.

Re: Incremental Load without Updated Date and Time

Anonymous — Wed, 09 Sep 2015 06:59:59 GMT

If I do that, no table is loaded into the Qlikview.

Re: Incremental Load without Updated Date and Time

Wed, 09 Sep 2015 07:01:23 GMT

Here am not understanding One thing

why you are trying to drop the IncTable. if we are drop the IncTable then there so no data to load in to QVD

Correct me if my understanding wrong

suggestion:comment the drop table command

Re: Incremental Load without Updated Date and Time

tresB — Wed, 09 Sep 2015 07:20:07 GMT

You need one more correction, remove the wrong table name from peek().

Let vmax=peek('MaxNumber',0,'MaxNumber');

Re: Incremental Load without Updated Date and Time

sunilkumarqv — Wed, 09 Sep 2015 07:23:43 GMT

I changed your script based on your requirement

Sample:

LOAD Number,

Alphabet

FROM Sample.xlsx (ooxml, embedded labels, table is Sheet1);

Store Sample into Sample.qvd(qvd);

I have inserted a new row to the Sample.xlsx as

Number	Alphabet
1	A
2	B
3	C
4	D
5	E

For performing Incremental Load (Insert and Update) on this data, I have added the following code to the existing Code.

MaxNumber:

LOAD max(Number) as Last_Updated_Number

Resident Sample;

Let vmax=peek('Last_Updated_Number',0,'MaxNumber');

Drop Table MaxNumber;

Drop Table Sample;

IncTable:

LOAD Number,

Alphabet

FROM Sample.xlsx

(ooxml, embedded labels, table is Sheet1) where Number > $(vmax);

Concatenate

LOAD Number,

Alphabet

FROM Sample.qvd (qvd) where not Exists(Number);

Re: Incremental Load without Updated Date and Time

Ralf-Narfeldt — Wed, 09 Sep 2015 07:23:46 GMT

I think you should change your initial load to be from the QVD instead of XLSX, and then put the Store at the end of the script.

Load from QVD

Calculate MaxNumber and store to variable

Drop MaxNumber

Load from XLSX where Number > $(vmax);

Store to QVD

Re: Incremental Load without Updated Date and Time

Anonymous — Wed, 09 Sep 2015 07:47:47 GMT

Ah. What a Catastrophic mistake have I done! Thanks for helping out mate. Now it works properly.

Re: Incremental Load without Updated Date and Time

sasiparupudi1 — Wed, 09 Sep 2015 07:50:24 GMT

Try Like this

Sample:

LOAD Number,Alphabet

FROM Sample.xlsx (ooxml, embedded labels, table is Sheet1);

Store Sample into Sample.qvd(qvd);

MaxNumber:

LOAD max(Number) as Last_Updated_Number Resident Sample;

Let vmax=peek('Last_Updated_Number',0,'MaxNumber');

Drop Table MaxNumber,Sample;

IncTable:

LOAD Number,Alphabet

FROM Sample.qvd (qvd) ;

Concatenate

LOAD Number,Alphabet

FROM UpdatedSample.xlsx

(ooxml, embedded labels, table is Sheet1) where not Exists(Number,Number);

hth

Sasi

Re: Incremental Load without Updated Date and Time

Wed, 09 Sep 2015 08:04:35 GMT

PFA

Thanks

Manju

Re: Incremental Load without Updated Date and Time

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