Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Yes, I forgot that I did use Pkey consistently. I forgot to update that in my discussion post. I still got that error
Can you please be more specific using my example?
Thanks
Jeb
also let us know what concept you are trying
like insert only, insert and update or insert update and delete
Insert only
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...