Discussion Board for collaboration on QlikView Scripting.
New to incremental loading and getting hung up on the getting data after last update. It feels like a solution that is easy but I cannot figure it out. Hoping the Qlik community can help!
This is an Oracle DB I'm querying to
Load of UPDATEDATE field only from my QVD. Others not needed for comparison
//CMRSALES CMRSALES: LOAD UPDATEDATE FROM (qvd);
Create LastUpdate field in DATE format of the DB.
//LastUpdate LastUpdateTime: LOAD DATE(MAX(UPDATEDATE),'MM/DD/YYYY hh:mm:ss TT') as LastUpdate resident CMRSALES;
Set LastUpdateVar Variable to use for comparison
//LastUpdateVariable Let LastUpdateVar = peek('LastUpdate',0,'LastUpdateTime');
Comparison Load to get latest data from DB
LOAD *; SQL SELECT * FROM GFXR.CMRSALESPNL WHERE UPDATEDATE>$(LastUpdateVar);
I can see LastUpdateVar is correct. I've tried different variations of using UPDATEDATE as NUM(UPDATEDATE) or formatting
ErrorSource: OraOLEDB, ErrorMsg: ORA-00933: SQL command not properly ended
SQL SELECT *
WHERE UPDATEDATE>10/07/2015 11:59:19 PM
Hi, Try this.
SQL SELECT * FROM GFXR.CMRSALESPNL WHERE UPDATEDATE>'$(LastUpdateVar)';
Thank you Fernando. It feels like I am getting closer but still seeing this error.
ErrorSource: OraOLEDB, ErrorMsg: ORA-01843: not a valid month
WHERE UPDATEDATE >= '10/07/2015 11:59:19 PM'
I tried using both but neither solved the valid month issue
LOAD DATE(MAX(UPDATEDATE),'MM/DD/YYYY hh:mm:ss TT') as LastUpdate
LOAD DATE(MAX(UPDATEDATE),'DD/MM/YYYY hh:mm:ss TT') as LastUpdate
You should check the format of the field in oracle.
Oracle Timestamp literal format is 'YYYY-MM-DD hh:mm:ss TT'
You can try this.
LOAD MAX(UPDATEDATE) as LastUpdate
LOAD DATE(MAX(UPDATEDATE),'YYYY-MM-DD hh:mm:ss TT') as LastUpdate