topic Re: Comparing two dates in where clause of load statement in QlikView

Comparing two dates in where clause of load statement

Qlik_Enthu — Mon, 12 Jun 2017 08:16:53 GMT

Hi all,

I am trying to compare the source data date against the sysdate and written the below in where clause:

And DATE("CUSORD_DATE",'M-D-YYYY') >= DATE("SYSDATE"-1,'M-D-YYYY');

however I am getting error message:

SQL##f - SqlState: S1000, ErrorCode: 936, ErrorMsg: [Oracle][ODBC][Ora]ORA-00936: missing expression

SQL SELECT * FROM COS.CUSTOMER_ORDER WHERE "CUSORD_STAT" ='00' And DATE("CUSORD_DATE",'M-D-YYYY') >= DATE("SYSDATE"-1,'M-D-YYYY')

Can anyone correct me if there is a mistake? also should we use any other table to include SYSDATE?

Re: Comparing two dates in where clause of load statement

divya_anand — Mon, 12 Jun 2017 08:34:31 GMT

Hi Saimeenakshi,

Would it be possible to share a sample QVW?

Re: Comparing two dates in where clause of load statement

Qlik_Enthu — Mon, 12 Jun 2017 08:41:04 GMT

Hello Divya,

Just started with the QVW creation and data load. my QV Script has only one load statement with a where clause. Below is my partial load statement:

SQL SELECT * FROM COS.CUSTOMER_ORDER WHERE "CUSORD_STAT" ='00' And DATE("CUSORD_DATE",'M-D-YYYY') >= DATE("SYSDATE"-1,'M-D-YYYY');

Re: Comparing two dates in where clause of load statement

divya_anand — Mon, 12 Jun 2017 08:48:48 GMT

Hi Saimeenakshi,

I assume CUSORD_DATE is from "COS.CUSTOMER_ORDER". What about SYSDATE? Have you assigned a value to SYSDATE prior to the SQL Statement?

You may try and share the application even if there is no data or connection string.

Re: Comparing two dates in where clause of load statement

Qlik_Enthu — Mon, 12 Jun 2017 08:57:38 GMT

I have not assigned any value to Sysdate (assuming it will store today`s date from the system tables) now I think that's not correct. I try copy pasting my .qvw, but its not working nor I find an attachment option while replying to the thread...

Re: Comparing two dates in where clause of load statement

divya_anand — Mon, 12 Jun 2017 09:03:13 GMT

You should be able to attach and application using the "App" icon-

If you don't find it, please use the Advanced Editor-

Re: Comparing two dates in where clause of load statement

divya_anand — Mon, 12 Jun 2017 09:04:39 GMT

You should be using "today()" to fetch today's date.

Re: Comparing two dates in where clause of load statement

Qlik_Enthu — Mon, 12 Jun 2017 09:32:08 GMT

I don't find the attachment option in !App nor the advance editor feature

Re: Comparing two dates in where clause of load statement

divya_anand — Tue, 13 Jun 2017 05:42:37 GMT

You might want to try this when you are free-

And in the meantime,

It is difficult to help without a sample application or without a clear picture of the script you are using. However, you may try this and see if this works for your scenario-

Assuming that the Date format in the database is 'M-D-YYYY'

let vDate = Timestamp(Today(),'M-D-YYYY');

LOAD *;

SQL SELECT * FROM COS.CUSTOMER_ORDER

WHERE "CUSORD_STAT" ='00' And

CUSORD_DATE >= '$(vDate)';

Re: Comparing two dates in where clause of load statement

Qlik_Enthu — Tue, 13 Jun 2017 05:52:05 GMT

Hi Divya,

I somehow managed to do it by my self. below is my code:

LET vToday=Date(Today(1),'MM/DD/YYYY');

ODBC CONNECT TO [PT1025;DBQ=PT1025.IKEADT.COM] (XUserId is RQNcTBdJTSaA, XPassword is efNSaBdJTaNOTbAN);
//-------- Start Multiple Select Statements ------
LOAD USER_ID_REF,
CUSORD_NO,
CUSORD_STAT,
Date(CUSORD_DATE,'MM/DD/YYYY') As ORDER_DATE,
CUST_NO,
CUS_NAME,
CTY_CODE;
SQL SELECT * FROM COS.CUSTOMER_ORDER WHERE "CUSORD_STAT" ='00' AND "CUSORD_NO">=$(vToday);

Thank you for helping me on this!