Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
zagahmadi
Partner - Contributor
Partner - Contributor

Incremental load with Oracle Timestamp

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

12 Replies
Kushal_Chawda

Probably looks like  you need TO_TIMESTAMP here

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

 

zagahmadi
Partner - Contributor
Partner - Contributor
Author

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
Partner - Contributor
Partner - Contributor
Author

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
Partner - Contributor
Partner - Contributor
Author

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
Partner - Contributor
Partner - Contributor
Author

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