Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
(
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);
Error Message.
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 *
FROM GFXR.CMRSALESPNL
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
SQL SELECT *
FROM GFXR.CMRSALESPNL
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
and
LOAD DATE(MAX(UPDATEDATE),'DD/MM/YYYY hh:mm:ss TT') as LastUpdate
Bryan,
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
or
LOAD DATE(MAX(UPDATEDATE),'YYYY-MM-DD hh:mm:ss TT') as LastUpdate
Best Regards.
Tonial.