Comparing Date column with variable in where clause

[sasqliksense]

Hi All,

Greetings..

1) I am using QlikSense v3.0, I created 2 sections one is Initial load and other is Delta Load.

2) In Initial load I am storing my "LoadTime" column into qvd file called "MaxLoadTime.qvd".

3) I am using MaxLoadTime.qvd and storing that value into variable "V_LoadTime" in my Delta load SQL statement, which looks like

4) In Intial load the format I stored is

MaxLoadTime:

LOAD TIMESTAMP(TIME(MAX("LoadTime"),'MM/DD/YYYY HH:MM:SS.FF AM')) AS MaxLoadTime

Resident DATA;

STORE MaxLoadTime INTO [lib://QDATA/MaxLoadTime.qvd];

5) In Delta load I am calling it as

UpdateLoadTime:

LOAD

MaxLoadTime FROM [lib://QDATA/MaxLoadTime.qvd]

(qvd);

LET V_LoadTime = peek('MaxLoadTime,0,UpdateLoadTime);

DROP TABLE UpdateLoadTime;

LIB CONNECT TO 'DATABASE'

DATA:

LOAD

Col1,

Col2,

Col3,

LoadTime;

Select

Col1,

Col2,

Col3,

LoadTime

from TableA

where LoadTime > $(V_LoadTime)

When I try to load the data I am getting error saying

ErrorSource: OraOLEDB, ErrorMsg: ORA-00936: missing expression: SQL

SELECT Col1,Col2,Col3,LoadTime from TableA

WHERE LoadTime > 9/21/2016 3:31:12 PM

Please help.

Sas

[swuehl]

try enclosing the variable expansion into single quotes:

where LoadTime > '$(V_LoadTime)'

Besides this, double check the format code section in the HELP, I think your format should look like

TIMESTAMP( MAX("LoadTime"),'MM/DD/YYYY hh:mm:ss.ff TT')

On Format Codes for Numbers and Dates

[sasqliksense]

Hi Stefan,

I applied the format you given in your reply, the date format in my variable is like "9/25/2016 09:35:09 AM", which is my MAX(LoadTime).

But when I update the LoadTime column in my table and try to run the Delta load it is not picking up the updated record.

My Query is

Select Col1, Col2, Col3, LoadTime

from TableA where 'LoadTime' > '$(V_LoadTime)';

The column LoadTime  datatype is Timestamp(6) in my oracle table

Please help

Regards

Sas

[swuehl]

Try using the to_timestamp() function:

Select Col1, Col2, Col3, LoadTime

from TableA where LoadTime > to_timestamp('$(V_LoadTime)', 'MM/DD/YYYY HH:MI:SS AM');

[amar_nath_etta]

this looks like old post but me to  facing same issue. any solution were you able to find.

In incremental load where clause  date columns is to be compared with variable of max date as mentioned.

Date_aof_aft > '$(V_Date_aof_aft_qvd)' 
Date_aof_aft= Date column