topic Re: Date format for incremental load in QlikView

Date format for incremental load

raju_salmon — Thu, 10 Nov 2016 06:43:52 GMT

Is Hi,

I have always confusion on date formats of DB and QV. While doing incremental load, what format we need to follow.

I have records in Oracle with below format

01-APR-16 05.23.09.992743000 AM

11-MAR-16 02.32.15.126544000 PM

In QV environment variables formats is available like below

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

While fetching incremental inserted block i am trying with below code.

TABLE1:

LOAD

MAX(INSERTED_DATE) AS MAXDATE

FROM [..\Data\Sessions_BaseData.qvd](qvd);

LET v_LastReloadDateTime = Timestamp((PEEK('MAXDATE', 0, TABLE1)),'DD-MMM-YY hh:mm:ss.fffffffff TT');

DROP TABLE TABLE1;

Is that right way to follow? Or do i need to follow QlikView format?

Many Thanks,

Raju

Re: Date format for incremental load

tamilarasu — Thu, 10 Nov 2016 06:57:36 GMT

Hi Raju,

You can use Date# function to interpret the date field like below

TABLE1:

LOAD Max(Date#(INSERTED_DATE,'DD-MMM-YY hh.mm.ss.fffffffff TT')) AS MAXDATE

FROM

Accumulation.xlsx

(ooxml, no labels, table is Input2);

LET v_LastReloadDateTime = Timestamp((PEEK('MAXDATE', 0, TABLE1)),'DD-MMM-YY hh:mm:ss.fffffffff TT');

DROP TABLE TABLE1;

Re: Date format for incremental load

raju_salmon — Thu, 10 Nov 2016 07:11:01 GMT

Thanks, Is there any specific reason to do this?

Re: Date format for incremental load

tamilarasu — Thu, 10 Nov 2016 07:33:44 GMT

If a date field is stored as a string format, Qlikview won't be able to recognise it as date field. So we are telling Qlikview that the field (INSERTED_DATE) format is DD-MMM-YY hh.mm.ss.fffffffff TT using the date# function. In your case, first we interpret the date field by mentioning the date format and finding the max value.

Below links might be helpful to understand this better.

QlikView Addict: Dates in QlikView - Part 2

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/InterpretationFunctions/interpretation-fun…

Re: Date format for incremental load

raju_salmon — Thu, 10 Nov 2016 08:39:36 GMT

Great. Thank for very helpful explanation.

Re: Date format for incremental load

raju_salmon — Thu, 10 Nov 2016 09:34:30 GMT

So, I understand that we need to follow the DB format while creating variable. Right?

Re: Date format for incremental load

tamilarasu — Thu, 10 Nov 2016 14:48:56 GMT

Raju,

You need to specify the format while loading the data. Then max function returns the max date. Finally, you can store the date in a variable then change the format using the formatting function i.e Date(Field_Name,'YourFormat') or Timestamp(Field_Name,'YourFormat').

You can also try like below. Both should work.

TABLE1:

LOAD Date(Max(Date#(INSERTED_DATE,'DD-MMM-YY hh.mm.ss.fffffffff TT')), 'DD-MMM-YY hh:mm:ss.fffffffff TT') AS MAXDATE

FROM

Accumulation.xlsx

(ooxml, no labels, table is Input2);

LET v_LastReloadDateTime = PEEK('MAXDATE', 0, TABLE1));

DROP TABLE TABLE1;

Re: Date format for incremental load

raju_salmon — Fri, 11 Nov 2016 04:14:15 GMT

Thanks Nag , Does this AM/PM format creates any issue? Because when i am trying to load using where clause, it is taking lot of time to filter the data.

Re: Date format for incremental load

tamilarasu — Fri, 11 Nov 2016 04:25:32 GMT

I don't think so. Could you post your scipt here?

Re: Date format for incremental load

raju_salmon — Fri, 11 Nov 2016 04:57:24 GMT

Something like this..

In below script, TT is available in format. Because DB format is "11-MAR-16 02.32.15.126544000 PM"

TABLE1:

LOAD

MAX(INSERTED_DATE) AS MAXDATE

FROM [..\Data\Sessions_BaseData.qvd](qvd);

LET v_LastReloadDateTime = Timestamp((PEEK('MAXDATE', 0, TABLE1)),'DD-MMM-YY hh:mm:ss.fffffffff TT');

DROP TABLE TABLE1;

Re: Date format for incremental load

tamilarasu — Fri, 11 Nov 2016 05:16:48 GMT

Just a question. You want to load max date data into Qlikview, right? If so, timestamp is not needed in ths case.

Re: Date format for incremental load

raju_salmon — Fri, 11 Nov 2016 06:41:36 GMT

Is it? I want to take incremental block data is from max date of existing QVD to current sysdate.

Re: Date format for incremental load

tamilarasu — Fri, 11 Nov 2016 07:31:54 GMT

Something like below.

TABLE1:
LOAD Date(Max(Date#(Subfield(INSERTED_DATE,' ',1),'DD-MMM-YY')),'DD-MMM-YY') AS MAXDATE
Resident
Data;

LET v_LastReloadDateTime = PEEK('MAXDATE', 0, TABLE1);

Drop Table TABLE1;

Final:
Load * From Source Where Date(Date#(Subfield(INSERTED_DATE,' ',1),'DD-MMM-YY'),'DD-MMM-YY')> '$(v_LastReloadDateTime)';

Re: Date format for incremental load

raju_salmon — Fri, 11 Nov 2016 08:44:19 GMT

Then it will take old record also... right? I need to use where not exist to exclude already available records.

Re: Date format for incremental load

tamilarasu — Fri, 11 Nov 2016 08:58:59 GMT

Raju,

This will load only new records. Below links might be helpful to understand about incremental load.

Incremental Load in QlikView – Part1 – Learn QlikView

Incremental Load in QlikView – Part2 – Learn QlikView

Could you explain what you are trying to acheive? If possible, please attach some sample data. It would be really helpful for me to guide you.