Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Incremental Load

I'm struggling with the incremental load. My load script looks like this.

ODBC CONNECT TO [A2i Server] (XUserId is CECJdQJGJTWB, XPassword is NJNTWBFMELZcWRVMRZHA);

LOAD *;

SQL SELECT  cast([recid] as varchar)+'-'+cast(cast([Transaction Effective Date] as int) as varchar) as PKey, *

FROM "GL_Transactions".dbo."GL_Transactions"

where [Transaction Effective Date] >='12/31/2015' ;

CONCATENATE(GL_Transactions)

LOAD *;

SQL SELECT *

FROM "GL_Transactions".dbo."GL_Transactions"

where not exists (PKey) ;

store GL_Transactions Into \\bhmwpapp1\qlikview\99.Shared_Folders\2.QVD\2.QVD_Data\GL_Transactions.qvd  (qvd);

It loads 17 million records for the whole set, but what I would like to do is load the 17 million records once and then only load 30,000 records each day, but I'm getting this error message

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

SQL SELECT *

FROM "GL_Transactions".dbo."GL_Transactions"

where not exists (PKey)

Can anyone suggest a better way to load the bulk one time and then the incremental records on a daily basis (ideally without manual intervention)?


Thanks

Jeb

7 Replies
eduardo_dimperi
Valued Contributor II

Re: Incremental Load

Hi Jeb,

About your problem,

You can make two qvd's only with the PKey Field, one with 17 million records and other with 30,000 records

First 30,000

Load

PK

from

somewhere;

and use this qvd in your select

After you dismiss this 30,000 from the 17 million qvd

Base_QVD:

LOAD

PK

YOUR_QVD_PLACE (qvd);

WHERE NOT EXISTS (PK);

To the next day you will your base qvd equal 17 million - 30,000 records to start again.

tajmohamed30
Contributor III

Re: Incremental Load

Try to load without any extra functions on key formation in SQL statement like below,

recid || '-' || Transaction Effective Date as Key.


Also keep the Link key names unique

if you use 'Key' then use only 'Key' in key formation, similarly

if you use 'PKey' then use only 'Pkey' in key formation

Not applicable

Re: Incremental Load

Yes, I forgot that I did use Pkey consistently. I forgot to update that in my discussion post. I still got that error

Not applicable

Re: Incremental Load

Can you please be more specific using my example?

Thanks

Jeb

tajmohamed30
Contributor III

Re: Incremental Load

also let us know what concept you are trying

like insert only, insert and update or insert update and delete

Not applicable

Re: Incremental Load

Insert only

RonaldDoes
Contributor III

Re: Incremental Load

Hi Jeb,

Also, keep in mind that everything after "SQL" needs to be valid SQL and "where not exists (PKey) " is not, because I believe it expects a SQL subquery.

LOAD *;

SQL SELECT *

FROM "GL_Transactions".dbo."GL_Transactions"

where not exists (PKey) ;

Mind you, I could be wrong, I'm not a SQL genius, just made this error a couple of times myself...

Community Browser