Incremental Load

[#1]

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

[eduardo_dimperio]

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]

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

[#4]

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

[#5]

Can you please be more specific using my example?

Thanks

Jeb

[tajmohamed30]

also let us know what concept you are trying

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

[#7]

Insert only

[RonaldDoes]

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