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]

Probably looks like  you need TO_TIMESTAMP here

WHERE "TIMESTAMP" > TO_TIMESTAMP('$(MaxID)', 'DD-Mon-RR HH24:MI:SS.FF');

[zagahmadi]

Thanks for the reply. TO_TIMESTAMP doesn't work for me. I get an error that the 'Mon' format isn't accepted.

[Kushal_Chawda]

@zagahmadi  try to change below then use to_timestamp

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

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

[Kushal_Chawda]

@zagahmadi  can you share the script you are using with exact error?

[zagahmadi]

Hi Kush, The script is included in my original post. Maybe I'm misunderstanding your question?

When I try using timestamp to reformat the MaxID in the select statement I get the following error:  ERROR [HY000] ORA-01843: not a valid month

When I use timestamp when I assign MaxID = timestamp(peek('MaxID',0,MaxKeyLoad),'MM/DD/YYYY hh:mm:ss') the script completes but then my dashboard breaks and returns this error: "The selections generated no data for this chart"

[Kushal_Chawda]

@zagahmadi  I mean copy paste the entire script which you are using as per my suggestions

[zagahmadi]

Hi Kush,

That is the full incremental load script that I am using. Is something missing? Below is the script from the initial load

LIB CONNECT TO 'Oracle_db';

// Get AuditTrail
[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];
SELECT
"ID",
"TIMESTAMP",
"AGGREGATEIDENTITYFULLNAMEID",
"AGGREGATEIDENTITYID",
"ENTITY",
"IDENTITYID",
"IDENTITYTYPEFULLNAMEID",
"FIELD",
"STEP",
"RESOLUTIONKIND",
"OLDVALUE",
"NEWVALUE",
"DATASOURCE",
"USERNAME",
"MESSAGEID",
"DESCRIPTION",
"DESCRIPTION_FALLBACK",
"PROCESSID"
FROM "V_AUDITTRAIL";

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

[Kushal_Chawda]

@zagahmadi  Where is incremental logic script?

[zagahmadi]

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);