Incremental Load and Refresh QVD with date and time

[reshmakala]

Hello all,

I am new to QlikView. I had a problem in doing incremental load. I have created a QVD for a table and the data needs to be refreshed everyday. I have a update date in my table which is in the format of 'MM/DD/YYYY hh:mm:ss' . If I use today() function I get the date in the same format. How can I do incremental load when I have update date in 'MM/DD/YYYY hh:mm:ss' format? If I use date() function in Where condition it says missing right parentheses. Please help me out.

[reshmakala]

This is the error that I am getting

[reshmakala]

Thanks for taking time to solve my issue. If I use this... the error is missing expression.

[maxgro · Accepted solution]

is your Oracle field a date (date and time) or a timestamp?

for date this works, you can adapt to your script (see comment about oracle vs Qlik format)

hope it helps

OLEDB CONNECT32 TO .........;

directory;

// first run, not incremental

IF NOT Alt(FileSize('abc.qvd'),0) >0 THEN

  TRACE 'file doesnt exists';

  abc:

  load

  COUNTRY as Country, REGION as Region, UPDATEDATE as UpdateDate, UPDATEDATE2 as UpdateDate2;

  SELECT

  COUNTRY, REGION, UPDATEDATE, UPDATEDATE2

  FROM ZZZ

  // to_date is an oracle function, DD/MM/YYY is oracle format

  WHERE UPDATEDATE >= to_date('01/01/2000', 'DD/MM/YYYY');

  STORE abc INTO abc.qvd (qvd);

// incremental

ELSE

  trace 'file exists';

  abc:   LOAD * from abc.qvd(qvd);

  Temp:   LOAD UpdateDate Resident abc   Order by UpdateDate;

  // vUpdate has Qlik format TimestampFormat, mine is SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

  Let vUpdate = Peek('UpdateDate',-1,'Temp');

  TRACE vUpdate=$(vUpdate);

  Concatenate (abc)

  load

  COUNTRY as Country, REGION as Region, UPDATEDATE as UpdateDate, UPDATEDATE2 as UpdateDate2;

  SELECT

  COUNTRY, REGION, UPDATEDATE, UPDATEDATE2

  FROM ZZZ

  // from Qlik string in TimestampFormat to Oracle date (here format is Oracleformat, MI, not mm)

  WHERE UPDATEDATE > to_date('$(vUpdate)', 'DD/MM/YYYY HH:MI:SS');

  STORE abc INTO abc.qvd (qvd);

ENDIF;

[reshmakala]

Thank you for the detailed script. I get the below error with the code you have suggested. My UpdateDate field is of DATE type. Its Oracle DB..

[robert99]

Does this blog post help

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

[reshmakala]

The script executes now. I made two minor changes. My timestamp format was set to m/d/yyyy h:mm:ss[.fff] TT... I removed TT and tried.

second change was in oracle time format I put HH24:MI:SS so that it could read time in 24 hour format and it works fine now.

Thanks a lot for your assistance!

[maxgro]

Did you get the error in the incremental (else) part?

here?

  WHERE UPDATEDATE >  to_date('$(vUpdate)', 'DD/MM/YYYY HH:MI:SS');

My script was tested on an Oracle db with a date field

This is my Qlik

DateFormat       and TimestampFormat

DD/MM/YYYY    DD/MM/YYYY hh:mm:ss[.fff]

I think you get the error because of different Qlik format

You should match the format of vUpdate Qlik variable with the Oracle format in to_date function (bold)

Example: in my Qlikscript

vUpdate is  '14/09/2014 09:54:52'     --> DD/MM/YYYY hh:mm:ss

so I used the Oracle format  DD/MM/YYYY HH:MI:SS'   in to_date() function

If your vUpdate is in a different format, change the bold part according your format

Example: if in your  Qlik script

vUpdate is  '09/14/2014 09:54:52'     --> MM/DD/YYYY hh:mm:ss

try with Oracle format  MM/DD/YYYY HH:MI:SS'   in to_date() function

[reshmakala]

Ya... my region needs it in MM/DD/YYYY and I did change that. thanks!

[reshmakala]

Thanks!

[#11]

I am looking for this ans for long time great work!!