topic Re: Date conversion and comparison in Where clause not successfull in QlikView

Date conversion and comparison in Where clause not successfull

Qlik_Enthu — Thu, 15 Jun 2017 09:03:09 GMT

Dear All,

Below is my code. I have tried all the possible combos of date conversion functions to compare today`s date (mm/dd/yyyy) with a date field from the data source., but it is just not working. The date field is in ts format and I am finding it difficult to convert to mm/dd/yyyy format. ITs quite urgent and help on this is very much appreciated.

LET vToday=Today(1);
ODBC CONNECT TO [PT1025;DBQ=PT1025.IKEADT.COM] (XUserId is RQNcTBdJTSaA, XPassword is efNSaBdJTaNOTbAN);
ODBC CONNECT TO [PT1028;DBQ=PT1028.IKEADT.COM] (XUserId is PTdbUBdJTaFB, XPassword is cIGOeBdJTaNOTbYU);
QUALIFY *;
UNQUALIFY CUSORD_NO;
//-------- Start Multiple Select Statements ------
"CUST_ORDER_EU":
LOAD USER_ID_REF,
CUSORD_NO,
CUSORD_STAT,
Date(CUSORD_DATE,'MM/DD/YYYY'),
CUST_NO,
CUS_NAME,
CTY_CODE;
SQL SELECT
USER_ID_REF,
CUSORD_NO,
CUSORD_STAT,
Convert(CUSORD_DATE,101),
CUST_NO,
CUS_NAME,
CTY_CODE
FROM COS.CUSTOMER_ORDER WHERE "CUSORD_STAT" ='00' AND "CUSORD_DATE" >=$(vToday);

Re: Date conversion and comparison in Where clause not successfull

prma7799 — Thu, 15 Jun 2017 09:09:16 GMT

Try like this

Date(Date#(CUSORD_DATE,'mm/dd/yyyy'),'MM/DD/YYYY') as NewDate

Re: Date conversion and comparison in Where clause not successfull

Qlik_Enthu — Thu, 15 Jun 2017 09:15:12 GMT

This has to be added in SQL part or in Load?

Thank you!

Re: Date conversion and comparison in Where clause not successfull

prma7799 — Thu, 15 Jun 2017 09:16:46 GMT

Can you share sample output ...

Or you can share format for the dates

Re: Date conversion and comparison in Where clause not successfull

Qlik_Enthu — Thu, 15 Jun 2017 09:22:47 GMT

hi below is my data preview loaded from the table:

Re: Date conversion and comparison in Where clause not successfull

Qlik_Enthu — Thu, 15 Jun 2017 09:23:52 GMT

and I have variable which stores the value of today(1) in mm/dd/yyyy format. this has to be compared with the cusord_date value

Re: Date conversion and comparison in Where clause not successfull

prma7799 — Thu, 15 Jun 2017 09:26:19 GMT

Try like this

date((Timestamp(CUSORD_DATE) ),'DD-MM-YYYY')

Re: Date conversion and comparison in Where clause not successfull

Qlik_Enthu — Thu, 15 Jun 2017 09:34:10 GMT

Not working!

Error: SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "ORDER_DATE": invalid identifier

My Load and SQL code:

"CUST_ORDER_EU":
LOAD USER_ID_REF,
CUSORD_NO,
CUSORD_STAT,
CUSORD_DATE,
Date((Timestamp(CUSORD_DATE)),'MM/DD/YYYY') As ORDER_DATE,
CUST_NO,
CUS_NAME,
CTY_CODE;
SQL SELECT
USER_ID_REF,
CUSORD_NO,
CUSORD_STAT,
CUSORD_DATE,
CUST_NO,
CUS_NAME,
CTY_CODE
FROM COS.CUSTOMER_ORDER WHERE "CUSORD_STAT" ='00' AND "ORDER_DATE" >=$(vToday);

Re: Date conversion and comparison in Where clause not successfull

Anil_Babu_Samineni — Thu, 15 Jun 2017 09:35:39 GMT

Would you able to provide sample data

Re: Date conversion and comparison in Where clause not successfull

prma7799 — Thu, 15 Jun 2017 09:42:55 GMT

first try to load only table and convert it without any changes into qvd then change date format

Like this

NewTable:
LOAD USER_ID_REF,
CUSORD_NO,
CUSORD_STAT,
CUSORD_DATE,
CUST_NO,
CUS_NAME,
CTY_CODE;
SQL SELECT
USER_ID_REF,
CUSORD_NO,
CUSORD_STAT,
CUSORD_DATE,
CUST_NO,
CUS_NAME,
CTY_CODE
FROM COS.CUSTOMER_ORDER WHERE "CUSORD_STAT" ='00' AND "ORDER_DATE" >=$(vToday);

Re: Date conversion and comparison in Where clause not successfull

jonathandienst — Thu, 15 Jun 2017 09:43:25 GMT

ORDER_DATE does not exist in the SQL source, and there is some confusion regarding date formats and Date() <format> and Date#() <interpret> functions. You say MM/DD/YYYY, but your sample above looks like M/D/YYYY. This code will fix those issues:

Let vToday = Date(Today(), 'M/D/YYYY'); // or MM/DD/YYYY

CUST_ORDER_EU:

LOAD USER_ID_REF,

CUSORD_NO,

CUSORD_STAT,

CUSORD_DATE,

Date#(SubField(CUSORD_DATE, ' ', 1), 'M/D/YYYY') As ORDER_DATE, // strip time and interpret

CUST_NO,

CUS_NAME,

CTY_CODE;

SQL SELECT

USER_ID_REF,

CUSORD_NO,

CUSORD_STAT,

CUSORD_DATE,

CUST_NO,

CUS_NAME,

CTY_CODE

FROM COS.CUSTOMER_ORDER WHERE CUSORD_STAT ='00' AND CUSORD_DATE >= '$(vToday)';

Re: Date conversion and comparison in Where clause not successfull

Qlik_Enthu — Thu, 15 Jun 2017 09:44:10 GMT

Anil, I am loading this data from DB and not sure I can pull out the sample data bcoz there are millions of records.

Re: Date conversion and comparison in Where clause not successfull

Qlik_Enthu — Thu, 15 Jun 2017 09:44:45 GMT

is there anything wrong with my script?

Re: Date conversion and comparison in Where clause not successfull

Qlik_Enthu — Thu, 15 Jun 2017 09:52:47 GMT

Jonathan, tried the above and getting the following error:

SQL##f - SqlState: S1000, ErrorCode: 932, ErrorMsg: [Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SQL SELECT
USER_ID_REF,
    CUSORD_NO,
    CUSORD_STAT,
    CUSORD_DATE,
    CUST_NO,
    CUS_NAME,
    CTY_CODE
FROM COS.CUSTOMER_ORDER WHERE "CUSORD_STAT" ='00' AND "CUSORD_DATE" >=06/15/2017

Re: Date conversion and comparison in Where clause not successfull

prma7799 — Thu, 15 Jun 2017 10:01:36 GMT

Try like this

"CUSORD_DATE" > = '06/15/2017';

Re: Date conversion and comparison in Where clause not successfull

Qlik_Enthu — Thu, 15 Jun 2017 10:18:01 GMT

PM actually the date variable holding today`s date works fine..the problem is with the sql conversion only. However tried this and no luck! says "not a valid month"

Re: Date conversion and comparison in Where clause not successfull

prma7799 — Thu, 15 Jun 2017 10:28:14 GMT

Please check your date format in database.

Re: Date conversion and comparison in Where clause not successfull

prma7799 — Thu, 15 Jun 2017 10:33:35 GMT

I am not sure but try like this ...

"CUSORD_DATE" >= SYSDATE

Re: Date conversion and comparison in Where clause not successfull

Qlik_Enthu — Thu, 15 Jun 2017 10:39:31 GMT

I have initially tried this. This will execute but wont fetch any records bcoz SYSDATE resides in DUal table. In my DB Dual table is dummy and doesn't have any fields nor data.

Re: Date conversion and comparison in Where clause not successfull

prma7799 — Thu, 15 Jun 2017 10:42:42 GMT

Try to check Description of table

Describe COS.CUSTOMER_ORDER;