topic Re: Incremental Load and Refresh QVD with date and time in QlikView

Incremental Load and Refresh QVD with date and time

reshmakala — Sat, 13 Sep 2014 21:30:10 GMT

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.

Re: Incremental Load and Refresh QVD with date and time

JonnyPoole — Sun, 14 Sep 2014 03:38:56 GMT

Can you upload the load script where you are getting the syntax error ?

Re: Incremental Load and Refresh QVD with date and time

Sun, 14 Sep 2014 04:12:27 GMT

I would save the 'timestamp(today())' in the variable. and use the variable in the where condition. Hope this would work for you.

Thank you

Suraj

Re: Incremental Load and Refresh QVD with date and time

Yousef_Amarneh — Sun, 14 Sep 2014 04:53:54 GMT

if you are using Date() function in the where clause while reading from SQL server or oracle you will get an error because you have to use cast function or convert or whatever depends on the platform you use.

Re: Incremental Load and Refresh QVD with date and time

reshmakala — Sun, 14 Sep 2014 05:26:57 GMT

Please find the load script

QVDLoad:

LOAD * from abc.qvd(qvd);

Temp:

LOAD UpdateDate Resident QVDLoad

Order by UpdateDate;

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

Concatenate(QVDLoad)

SELECT *

FROM "Tablename"

WHERE (timestamp("UpdateDate") > Timestamp('$(vUpdate)')) AND (Country = 'US');

Please help me to solve this. I use Oracle Database.

Re: Incremental Load and Refresh QVD with date and time

reshmakala — Sun, 14 Sep 2014 05:27:25 GMT

Thank you for your reply. Timestamp - invalid identifier is the error that I am getting.

Re: Incremental Load and Refresh QVD with date and time

reshmakala — Sun, 14 Sep 2014 05:27:51 GMT

Yes...I am using Oracle Database. I get error for any function used in WHERE clause.

Re: Incremental Load and Refresh QVD with date and time

Yousef_Amarneh — Sun, 14 Sep 2014 05:38:23 GMT

try this

WHERE (To_Date(UpdateDate','MM/DD/YYYY hh:mm:ss') > Timestamp('$(vUpdate)')) AND (Country = 'US');

Re: Incremental Load and Refresh QVD with date and time

reshmakala — Sun, 14 Sep 2014 06:05:08 GMT

I get this error now.

Re: Incremental Load and Refresh QVD with date and time

Yousef_Amarneh — Sun, 14 Sep 2014 06:07:39 GMT

I don't have oracle to test the query but try this

WHERE (To_Date(UpdateDate','MM/DD/YYYY hh:mm:ss') > To_Date('$(vUpdate)','MM/DD/YYYY hh:mm:ss')) AND (Country = 'US');

Re: Incremental Load and Refresh QVD with date and time

reshmakala — Sun, 14 Sep 2014 06:16:09 GMT

This is the error that I am getting

Re: Incremental Load and Refresh QVD with date and time

reshmakala — Sun, 14 Sep 2014 06:24:16 GMT

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

Re: Re: Incremental Load and Refresh QVD with date and time

maxgro — Sun, 14 Sep 2014 08:07:50 GMT

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;

Re: Incremental Load and Refresh QVD with date and time

reshmakala — Sun, 14 Sep 2014 13:19:28 GMT

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

Re: Incremental Load and Refresh QVD with date and time

robert99 — Sun, 14 Sep 2014 13:44:56 GMT

Does this blog post help

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

Re: Incremental Load and Refresh QVD with date and time

reshmakala — Sun, 14 Sep 2014 13:47:44 GMT

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!

Re: Re: Incremental Load and Refresh QVD with date and time

maxgro — Sun, 14 Sep 2014 13:49:01 GMT

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

Re: Incremental Load and Refresh QVD with date and time

reshmakala — Sun, 14 Sep 2014 14:03:03 GMT

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

Re: Incremental Load and Refresh QVD with date and time

reshmakala — Sun, 14 Sep 2014 14:45:19 GMT

Thanks!

Re: Incremental Load and Refresh QVD with date and time

Fri, 26 Sep 2014 11:58:20 GMT

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