topic Re: Incremental Load in QlikView

Incremental Load

Wed, 07 Jun 2017 13:47:17 GMT

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

Re: Incremental Load

eduardo_dimperio — Wed, 07 Jun 2017 14:12:42 GMT

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

from

somewhere;

and use this qvd in your select

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

Base_QVD:

LOAD

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.

Re: Incremental Load

tajmohamed30 — Wed, 07 Jun 2017 14:16:50 GMT

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

Re: Incremental Load

Wed, 07 Jun 2017 14:21:50 GMT

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

Re: Incremental Load

Wed, 07 Jun 2017 14:26:41 GMT

Can you please be more specific using my example?

Thanks

Jeb

Re: Incremental Load

tajmohamed30 — Wed, 07 Jun 2017 14:35:54 GMT

also let us know what concept you are trying

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

Re: Incremental Load

Wed, 07 Jun 2017 14:41:29 GMT

Insert only

Re: Incremental Load

RonaldDoes — Wed, 07 Jun 2017 14:41:40 GMT

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...