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]

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

[raju_salmon]

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

[tamilarasu]

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

[raju_salmon]

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

[tamilarasu]

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.