Incremental Load in Script

[vksthkkr]

I am trying to do an incremental load for a customer table from an SQL database into my local QVD file.

The SQL Table has Cust_ID as the primary key.

I am using the script as given below:

Customer:

LOAD * FROM (qvd);

Concatenate

SQL SELECT * FROM "DB_NAME".dbo."Customer" where NOT Exists(Cust_ID) ;

STORE Customer into "C:\PROJECTS\QLIKVIEW\CUSTOMER.QVD" ;

While reloading, I get an error:

SQL##f - SqlState: 37000, ErrorCode: 102, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Cust_ID'.

Concatenate

SQL SELECT * FROM "DB_NAME".dbo."Customer" where NOT Exists(Cust_ID)

Not sure what I am doing wrong here.

Please help.

[Gysbert_Wassenaar]

You're mixing SQL and Qlikview syntax. Your SQL database does not understand the Qlikview specific Exists syntax. Furtermore the SQL database cannot know which Cust_ID values Qlikview has already loaded. So you will will first have to load all the data from the SQL database into Qlikview before you can then filter out the Cust_ID's that you already loaded.

[Anonymous]

Hi Vikas,

Try below code hope it helps:

Customer:

LOAD * FROM (qvd);

Concatenate

LOAD *

where NOT Exists(Cust_ID) ;

SQL SELECT * FROM "DB_NAME".dbo."Customer"  ;

STORE Customer into "C:\PROJECTS\QLIKVIEW\CUSTOMER.QVD" ;

Regards

Nitha

[#4]

Hi,

Exists() is the qlikview syntax so sql it won't understand exists functionality.Here you can do using of preceding load which helps to the exists functionality.

Try this code:

Cust:

Load * from cust.qvd;

Concatenate

Load *

where not exist(custID);

sql select * from DBO>Cust;

store Cust into cust.qvd;

[simenkg]

I suggest changing the code to:

Cust:

Load *, custID as custIDRef from cust.qvd;

Concatenate

Load *

where not exist(custIDRef, custID);

sql select * from DBO>Cust;

drop field custIDRef;

store Cust into cust.qvd;

[vksthkkr]

Thanks Gysbert Wassenaar.

That was very helpful!

[vksthkkr · Accepted solution]

Yes. That worked!

Thanks Nitha P

[vksthkkr]

Thanks Simen Kind Gulbrandsen

[Anonymous]

The above incremental load "syntax only correct". but load time will not improve. As in preceeding load it will read entire sql table and then only it will verify the NOT EXISTS condition. so you are just skipping the rows loading in Qlikview internal table. But you are executing the sql with whole set of data again.

it seems it is a limitation with qlikview to read data from Sql TABLE, we cannot do incremental staregy unless we have load time stamp in your SQL TABLE.

BR,

Chinna