Incremental issue

[#1]

Hi,

I have one question about Incremental loading.

This is from QV help: Insert and Update. (No Delete)

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT Exists(PrimaryKey);

STORE QV_Table INTO File.QVD;

I was wondering what the meaning of WHERE NOT Exists(PrimaryKey); ?

Isaac

[pover · Accepted solution]

Basically it means that it will load rows from File.QVD that have a PrimaryKey that has not been loaded in the previous Load from DB_TABLE.

Regards.

[suniljain]

where not exitst(Primary key)

does not allow duplicate data to insert and update thae existing record if changed in source system.

Regards;

Sunil

[#4]

Many thanks for your explanation.

[#5]

Thank you very much indeed!!!

[quiquehm]

Hi, I am getting myself familiar with incremental loads , and I found the following "guidelines" in the Qlikview Notes blog ( http://qlikviewnotes.blogspot.co.uk/2012/01/incremental-load-using-qlikview.html 😞

To utilize Incremental Load a source table must have both of the following attributes:

•   A unique identifier -- a Primary Key -- for each row.

• A "Modification"  column that identifies when a row was added or updated. The column type may be a Date, Datetime or ascending Revision number.

  The classic IL logic is this: 

1. Determine the "Last reload Time". The most robust technique is to extract the max value for the "Modification" column from the Master QVD.

1. Select rows from the database table where "Modification" is greater than "Last Reload Time".
2. Add and update rows in the Master QVD, based on primary key.

My situation is as follows :

I am trying to setup an incremental load to build a QVD, but my scenario is a bit different. I have multiple excel sheets being stored in a folder ( these are periodic extractions from the same source system, so same fields everytime ), no primary key available ( not at least without building a composite key using several fields...which affects performance seriously..and I disregarded ); also there is not any datestamp column on these files, and the way I manually create a datestamp column is by using part of the file names in the Qview script. So every file stored I name it like : yyyymmdd-abc.xls ( being yyyymmdd the date when I get the extract and store it in the folder ).Then using a "for each a in" ..function I load these files in sequence adding a datestamp field like $(a) as DateStamp. Then all is stored in a QVD file. What I need to accomplish is everytime I do a data reload I need to avoid loading all the xls files again ( and building the whole QVD again )wasting time. I would need the script to only load the newer xls files stored in the folder. Any ideas on how to set it up ? I post attach a diagram with this ETL scenario for clarity

Thanks a lot

Enrique