topic Re: Comparing Date column with variable in where clause in QlikView

Comparing Date column with variable in where clause

sasqliksense — Wed, 21 Sep 2016 13:03:08 GMT

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

Re: Comparing Date column with variable in where clause

swuehl — Wed, 21 Sep 2016 19:35:48 GMT

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

Re: Comparing Date column with variable in where clause

sasqliksense — Sun, 25 Sep 2016 06:40:06 GMT

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

Re: Comparing Date column with variable in where clause

swuehl — Mon, 26 Sep 2016 20:19:39 GMT

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

Re: Comparing Date column with variable in where clause

amar_nath_etta — Sat, 17 Nov 2018 07:27:11 GMT

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