topic Re: Timestamp issue in incremental load in QlikView

Timestamp issue in incremental load

Fri, 27 Jun 2014 15:56:02 GMT

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?

Re: Timestamp issue in incremental load

Anonymous — Fri, 27 Jun 2014 18:11:02 GMT

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

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

and see if it solves the problem.

Regards.

Re: Timestamp issue in incremental load

Sat, 28 Jun 2014 09:30:12 GMT

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

Re: Timestamp issue in incremental load

Anonymous — Sat, 28 Jun 2014 17:55:53 GMT

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

rwunderlich — Sat, 28 Jun 2014 21:59:04 GMT

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