Date format for incremental load

[raju_salmon]

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

[tamilarasu]

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;

[raju_salmon]

Thanks, Is there any specific reason to do this?

[tamilarasu]

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/InterpretationFunction...

[raju_salmon]

Great. Thank for very helpful explanation.

[raju_salmon]

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

[tamilarasu]

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;

[raju_salmon]

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.

[tamilarasu]

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

[raju_salmon]

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;