Solved: Incremental Load and Refresh QVD with date and tim... - Qlik Community

reshmakala · ‎2014-09-13

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.

maxgro · ‎2014-09-14

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;

View solution in original post

JonnyPoole · ‎2014-09-13

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

Report Inappropriate Content · ‎2014-09-14

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

Yousef_Amarneh · ‎2014-09-14

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.

Yousef Amarneh

reshmakala · ‎2014-09-14

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.

reshmakala · ‎2014-09-14

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

reshmakala · ‎2014-09-14

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

Yousef_Amarneh · ‎2014-09-14

try this

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

Yousef Amarneh

reshmakala · ‎2014-09-14

I get this error now.

Yousef_Amarneh · ‎2014-09-14

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

Yousef Amarneh

Incremental Load and Refresh QVD with date and time