Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Timestamp issue in incremental load


Hi Guys,

This is my first question that I was not able to solve by reading your answers.

Within incremental load logic there is condition within SQL statement. The condition is as folowing:

where LASTMODIFIEDDATE >= '$(vLastExecTime)' and LASTMODIFIEDDATE < '$(vExecTime)'

while variables are filled as follows:

LET vExecTime = LocalTime();

IF (NOT isNull(QvdCreateTime('$(vQVDPath)M_LOANREQUEST.qvd'))) THEN
LoadTime:
LOAD Max(LASTMODIFIEDDATE) as LASTMODIFIEDDATE
FROM $(vQVDPath)M_LOANREQUEST.qvd (qvd);
LET vLastExecTime = peek('LastModifiedDate',0,'LoadTime');
DROP Table LoadTime;
END IF;

the issue is that I receive the latest date - end of day yesterday. So I suppose there is some missmatch in hour/minute/seconds formats in SQL and Qlikview.

this is format of date in QV

SET TimestampFormat='DD.MM.YYYY h:mm:ss[.fff]';

this is timestamp format in SQL developer

DD-MON-RR HH.MI.SSXFF AM

I have tried several options such as:

 

and LASTMODIFIEDDATE >= to_timestamp('$(vLastExecTime)','DD-MON-RR HH.MI.SSXFF AM TZR')

however I still receive just end of day yesterday. The source is updated online and this is very much needed.

Can you please help me how I can compare timestamps in SQL statements using variables?

4 Replies
bruno_montenegr
Contributor III

Re: Timestamp issue in incremental load

Hello,

There is a little problem with your script. You load the field LASTMODIFIEDDATE from the QVD but you try to read the field LastModifiedDate to save in the variable. Change the line


LET vLastExecTime = peek('LastModifiedDate',0,'LoadTime');

to


LET vLastExecTime = peek('LASTMODIFIEDDATE',0,'LoadTime');

and see if it solves the problem.

Regards.

Not applicable

Re: Timestamp issue in incremental load

Thank you Bruno,

Now I have to somehow fix the format. I think I am just not capable to create timestamp valid in SQL

this is the issue now:

 

ErrorSource: OraOLEDB, ErrorMsg: ORA-01843: not a valid month

...

where 1=1

and rank =1

and LASTMODIFIEDDATE >= '26.06.2014 23:45:14'

and LASTMODIFIEDDATE < '28.06.2014 11:01:04'

Thank you

bruno_montenegr
Contributor III

Re: Timestamp issue in incremental load

Hi, Matej.

So we have to figureout the timestamp format of your database. Let's try setting the variables like this:

LET vExecTime = Timestamp(LocalTime(), 'MM-DD-YYYY hh:mm:ss');

LET vLastExecTime = Timestamp(Peek('LASTMODIFIEDDATE'), 'MM-DD-YYYY hh:mm:ss');

If it doesn't work, try changing the 'MM-DD-YYYY hh:mm:ss' until it matches the DB timestamp format.

Re: Timestamp issue in incremental load

Oracle Timestamp literal format is

TIMESTAMP 'YYYY-MM-DD hh:mm:ss'

Note the string "TIMESTAMP" is part of the specification, that's not a QV function.

If you use Qlikview Components (QVC) to do your incremental loads, QVC has knowledge of the formats used by various databases.

-Rob

http://masterssummit.com

Community Browser