Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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
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.
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