topic Re: Incremental Load in Script in QlikView

Incremental Load in Script

vksthkkr — Thu, 25 Sep 2014 20:37:03 GMT

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.

Re: Incremental Load in Script

Gysbert_Wassenaar — Fri, 26 Sep 2014 11:07:49 GMT

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.

Re: Incremental Load in Script

Anonymous — Fri, 26 Sep 2014 13:15:42 GMT

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

Re: Incremental Load in Script

Fri, 26 Sep 2014 13:32:22 GMT

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;

Re: Incremental Load in Script

simenkg — Fri, 26 Sep 2014 13:43:51 GMT

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;

Re: Incremental Load in Script

vksthkkr — Fri, 26 Sep 2014 14:18:50 GMT

Thanks Gysbert Wassenaar.

That was very helpful!

Re: Incremental Load in Script

vksthkkr — Fri, 26 Sep 2014 14:21:02 GMT

Yes. That worked!

Thanks Nitha P

Re: Incremental Load in Script

vksthkkr — Fri, 26 Sep 2014 14:22:28 GMT

Thanks Simen Kind Gulbrandsen

Re: Incremental Load in Script

Anonymous — Wed, 22 Apr 2015 08:26:27 GMT

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