topic incremental load - insert only in QlikView

incremental load - insert only

arulsettu — Wed, 11 Jun 2014 08:16:03 GMT

i am trying to do incremental load only in my app.

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\Arul\Desktop\my project\CUSTOMERS.xls];

Customer:

SQL SELECT *

FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`;

STORE Customer into C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

Customer:

LOAD [Address Number],

[Business Family],

[Business Unit],

Customer,

[Customer Number],

[Customer Type],

Division,

Phone,

[Region Code],

[Regional Sales Mgr],

[Search Type];

SQL SELECT * FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`

WHERE [Customer Number] >='10027629';

while loading this i am getting error. can anyone help?

thanks...

Re: incremental load - insert only

its_anandrjs — Wed, 11 Jun 2014 08:19:13 GMT

What error you get and before full load try to debug the application

Re: incremental load - insert only

arulsettu — Wed, 11 Jun 2014 08:22:31 GMT

Re: incremental load - insert only

its_anandrjs — Wed, 11 Jun 2014 08:24:32 GMT

Try like Drop the table first

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\Arul\Desktop\my project\CUSTOMERS.xls];

Customer:

SQL SELECT *

FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`;

STORE Customer into C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

Drop Table Customer;

//And then load the excel file

Customer:

LOAD [Address Number],

[Business Family],

[Business Unit],

Customer,

[Customer Number],

[Customer Type],

Division,

Phone,

[Region Code],

[Regional Sales Mgr],

[Search Type];

SQL SELECT * FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`

WHERE [Customer Number] >='10027629';

Re: incremental load - insert only

tresB — Wed, 11 Jun 2014 08:25:32 GMT

Try where clause without quotes like:

WHERE [Customer Number] >=10027629;

Re: incremental load - insert only

alexandros17 — Wed, 11 Jun 2014 08:26:04 GMT

this is your mistake:

SQL SELECT * FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`

select is from tables, instead of sql select write load

Re: incremental load - insert only

its_anandrjs — Wed, 11 Jun 2014 08:29:32 GMT

In the where condition remove the single quotes (,)

WHERE [Customer Number] >= 10027629;

Re: incremental load - insert only

arulsettu — Wed, 11 Jun 2014 08:35:10 GMT

my QVD is getting update without concatenate how?

Re: incremental load - insert only

Wed, 11 Jun 2014 08:37:15 GMT

Cause you store select * into your QVD file

Re: incremental load - insert only

arulsettu — Wed, 11 Jun 2014 08:43:50 GMT

Hi Thomas

can u elaborate?

Re: incremental load - insert only

Wed, 11 Jun 2014 08:56:09 GMT

First you have to do an intial load

//INITIAL LOAD

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\Arul\Desktop\my project\CUSTOMERS.xls];

Customer:

SQL SELECT *

FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`;

STORE Customer into C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

Now all your data is stored into your customer.qvd - Then comment out the intial load part

MaxID:

LOAD Max([Customer Number]) as MaxIncrementalID

FROM

C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

LET vMaxIncrementalID = peek('MaxIncrementalID',0,'MaxID');

Now you need to do your incremental load

//INCREMENTAL LOAD

Customer:

LOAD [Address Number],

[Business Family],

[Business Unit],

Customer,

[Customer Number],

[Customer Type],

Division,

Phone,

[Region Code],

[Regional Sales Mgr],

[Search Type];

SQL SELECT * FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`

WHERE [Customer Number] >=$(vMaxIncrementalID);

Concatenate

LOAD

[Address Number],

[Business Family],

[Business Unit],

Customer,

[Customer Number],

[Customer Type],

Division,

Phone,

[Region Code],

[Regional Sales Mgr],

[Search Type]

FROM

C:\Users\Arul\Desktop\my project\Customer.qvd (qvd);

STORE Customer into C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

Re: incremental load - insert only

arulsettu — Wed, 11 Jun 2014 08:56:13 GMT

so why is the QVD getting updated without concatenate any help?

Re: incremental load - insert only

arulsettu — Wed, 11 Jun 2014 08:58:28 GMT

thanks i ll check and comeback to u

Re: incremental load - insert only

Wed, 11 Jun 2014 08:59:31 GMT

remember first to load the intial load (comment out all the other)

Re: incremental load - insert only

arulsettu — Wed, 11 Jun 2014 09:10:52 GMT

after initial load commenting initial load means this:

//Customer:

//SQL SELECT *

//FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`;

//STORE Customer into C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

Re: incremental load - insert only

Wed, 11 Jun 2014 09:12:50 GMT

Yes!

Re: incremental load - insert only

arulsettu — Wed, 11 Jun 2014 09:17:49 GMT

any reason for this

Re: incremental load - insert only

its_anandrjs — Wed, 11 Jun 2014 09:20:08 GMT

You can try with this way also

//Step One (Creating the QVD) After creating the QVD comment this step one it is only for initial QVD creation

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\Arul\Desktop\my project\CUSTOMERS.xls];

Customer:

SQL SELECT *

FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`;

STORE Customer into C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

DROP Table Customer;

///////////////////////////////////////////////////////////////////

//Step Two (Find out the Max Incremental Load ID

MaxIDTable:

LOAD Max([Customer Number]) as MaxIncrID

FROM

C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

LET vMaxIncrID = peek('MaxIncrID',0,'MaxIDTable');

//Step Three (Incremental Load Step and concatenate with the old data with QVD

Customer:

LOAD [Address Number],

[Business Family],

[Business Unit],

Customer,

[Customer Number],

[Customer Type],

Division,

Phone,

[Region Code],

[Regional Sales Mgr],

[Search Type];

SQL SELECT * FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`

WHERE [Customer Number] >=$(vMaxIncrID);

Concatenate

LOAD

[Address Number],

[Business Family],

[Business Unit],

Customer,

[Customer Number],

[Customer Type],

Division,

Phone,

[Region Code],

[Regional Sales Mgr],

[Search Type]

FROM

C:\Users\Arul\Desktop\my project\Customer.qvd (qvd);

STORE Customer into C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

DROP Table MaxIDTable;

Re: incremental load - insert only

Wed, 11 Jun 2014 09:21:39 GMT

Yes! If you wanna do incremental loads, you first need to load all your data. Hereby you can fetch the maximum ID you wanna use as an incremental identifier.

This ID you find the MAX from which you can use when you wanna load from your source.

So now you only get the fields which is greather than or equal to your maxID (If it was me i wanna remove the equal too, otherwise you might risk that you get dublets) - But i dont know your business logic.

When you load from your source with the maxid you only have the newest records. These you wanna concatenate with the old records from your QVD, and then you wanna store this into the QVD so you get all records.

Re: incremental load - insert only

arulsettu — Wed, 11 Jun 2014 09:30:11 GMT

thank u all