Incremental load with Oracle Timestamp

[zagahmadi]

Hi all, 

I know there are several posts on this topic, however, there does not appear to be a straight forward solution provided, especially for the current version of QlikSense..

I am attempting to set up an incremental load of an Oracle table that uses an Oracle timestamp ('DD-Mon-RR HH24:MI:SS.FF') as the key date. My script executes without an error, however, any dashboards referencing this table then break.

It seems to me that Qlik is unable to compare the Oracle timestamp to the MaxID date I created because of the date format. The Oracle timestamp format is not a valid format for Qlik. Is there a valid date format that will match the Oracle timestamp format and/or is there another method for incrementally loading based on an oracle timestamp? Script below:

LIB CONNECT TO 'Oracle_db';

MaxKeyLoad:
LOAD Max([TIMESTAMP]) as MaxID
FROM [Lib://QvdFiles/V_AUDITTRAIL.qvd] (qvd);

Let MaxID = Date(peek('MaxID',0,MaxKeyLoad),'MM/DD/YYYY hh:mm:ss');

[V_AUDITTRAIL]:
LOAD
[ID] AS [V_AUDITTRAIL.ID],
[TIMESTAMP],
[AGGREGATEIDENTITYFULLNAMEID],
[AGGREGATEIDENTITYID] AS [LINK-INSTRUMENTIDENTITYID-PERF_AGGID],
[ENTITY],
[IDENTITYID],
[IDENTITYTYPEFULLNAMEID],
[FIELD],
[STEP],
[RESOLUTIONKIND],
[OLDVALUE],
[NEWVALUE],
[DATASOURCE] AS [V_AUDITTRAIL.DATASOURCE],
[USERNAME],
[MESSAGEID],
[DESCRIPTION] AS [V_AUDITTRAIL.DESCRIPTION],
[DESCRIPTION_FALLBACK],
[PROCESSID] AS [V_AUDITTRAIL.PROCESSID];
SQL
SELECT * FROM "V_AUDITTRAIL"
WHERE "TIMESTAMP" > to_date('$(MaxID)', 'MM/DD/YYYY HH24:MI:SS');

CONCATENATE
LOAD
[V_AUDITTRAIL.ID],
[TIMESTAMP],
[AGGREGATEIDENTITYFULLNAMEID],
[LINK-INSTRUMENTIDENTITYID-PERF_AGGID],
[ENTITY],
[IDENTITYID],
[IDENTITYTYPEFULLNAMEID],
[FIELD],
[STEP],
[RESOLUTIONKIND],
[OLDVALUE],
[NEWVALUE],
[V_AUDITTRAIL.DATASOURCE],
[USERNAME],
[MESSAGEID],
[V_AUDITTRAIL.DESCRIPTION],
[DESCRIPTION_FALLBACK],
[V_AUDITTRAIL.PROCESSID]
FROM [Lib://QvdFiles/V_AUDITTRAIL.qvd] (qvd);

Loosen Table V_AUDITTRAIL;

STORE V_AUDITTRAIL INTO [Lib://QvdFiles/V_AUDITTRAIL.qvd] (qvd);

[Kushal_Chawda]

@zagahmadi  Try below

MaxKeyLoad:
LOAD Max([TIMESTAMP]) as MaxID
FROM [Lib://QvdFiles/V_AUDITTRAIL.qvd] (qvd);

Let MaxID = timestamp(peek('MaxID',0,MaxKeyLoad),'YYYY-MM-DD hh:mm:ss');

[V_AUDITTRAIL]:
LOAD
[ID] AS [V_AUDITTRAIL.ID],
[TIMESTAMP],
[AGGREGATEIDENTITYFULLNAMEID],
[AGGREGATEIDENTITYID] AS [LINK-INSTRUMENTIDENTITYID-PERF_AGGID],
[ENTITY],
[IDENTITYID],
[IDENTITYTYPEFULLNAMEID],
[FIELD],
[STEP],
[RESOLUTIONKIND],
[OLDVALUE],
[NEWVALUE],
[DATASOURCE] AS [V_AUDITTRAIL.DATASOURCE],
[USERNAME],
[MESSAGEID],
[DESCRIPTION] AS [V_AUDITTRAIL.DESCRIPTION],
[DESCRIPTION_FALLBACK],
[PROCESSID] AS [V_AUDITTRAIL.PROCESSID];
SQL
SELECT * FROM "V_AUDITTRAIL"
WHERE TO_TIMESTAMP(to_char("TIMESTAMP",'DD-Mon-RR HH24:MI:SS.FF') ,'YYYY-MM-DD HH24:MI:SS')> TO_TIMESTAMP('$(MaxID)', 'YYYY-MM-DD HH24:MI:SS');

CONCATENATE
LOAD
[V_AUDITTRAIL.ID],
[TIMESTAMP],
[AGGREGATEIDENTITYFULLNAMEID],
[LINK-INSTRUMENTIDENTITYID-PERF_AGGID],
[ENTITY],
[IDENTITYID],
[IDENTITYTYPEFULLNAMEID],
[FIELD],
[STEP],
[RESOLUTIONKIND],
[OLDVALUE],
[NEWVALUE],
[V_AUDITTRAIL.DATASOURCE],
[USERNAME],
[MESSAGEID],
[V_AUDITTRAIL.DESCRIPTION],
[DESCRIPTION_FALLBACK],
[V_AUDITTRAIL.PROCESSID]
FROM [Lib://QvdFiles/V_AUDITTRAIL.qvd] (qvd);

Loosen Table V_AUDITTRAIL;

STORE V_AUDITTRAIL INTO [Lib://QvdFiles/V_AUDITTRAIL.qvd] (qvd);

You can also try to replace TO_TIMESTAMP with to_date in above described where condition. But do not change anything apart from that

[zagahmadi]

Hi, using TO_TIMESTAMP in the WHERE you provided, I now receive the following : ERROR [HY000] ORA-01830: date format picture ends before converting entire input string

If I change it to TO_DATE, I receive this error message instead : ERROR [HY000] ORA-01858: a non-numeric character was found where a numeric was expected

[Kushal_Chawda]

@zagahmadi  If you are using the exact script I have given it should work. I am not sure what's wrong there. Probably you can try changing format