Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

ODBC connection - apply a WHERE on loading

I have an ODBC connection in my QlikSense Desktop app.  There is a table of employees, one of the fields is 'terminationDate'; I want to create a cliksense table for just the employees with a termination date.  The field contains either a date or is blank. However, when you preview the table via Data Model Viewer that field shows as -

termdate preview.JPG

This data seems to be causing me issues, as even in a visualization formula I can't get that data to be recognized.

eg. Count(if(terminationDate='',1,)) yields 0

also Count(if(terminationDate='-',1,)) yields 0

While I add a WHERE as part of the load script I get an errors

[Enders]:

LOAD

    "code" as EndEmpCode,

//     "oid" as EmpOID,

    "usualName" as EndName,

//     "Type" as EndType,

//     Basis as EndBasis,

    "position" as EndPosition,

    Date("terminationDate",'dd/MM/yyyy') as Date;

SQL SELECT

  "code",

    "usualName",

    "terminationDate",

    "position"

FROM HRPerson

WHERE Num(terminationDate)<>'';

I Get:

The following error occurred:

SQL##f - SqlState: 37000, ErrorCode: 8258, ErrorMsg: [Jade Software Corporation][JADE ODBC Server] Expecting: 'comparison operator'; found '(' at 100

[Enders]:

LOAD

    "code" as EndEmpCode,

//     "oid" as EmpOID,

    "usualName" as EndName,

//     "Type" as EndType,

//     Basis as EndBasis,

    "position" as EndPosition,

    Date("terminationDate",'dd/MM/yyyy') as Date;

SQL SELECT

  "code",

    "usualName",

    "terminationDate",

    "position"

FROM HRPerson

WHERE terminationDate<>'';

I get:

The following error occurred:

SQL##f - SqlState: 42000, ErrorCode: 8275, ErrorMsg: [Jade Software Corporation][JADE ODBC Server] Invalid combination of types compared: DATE and VARCHAR

Any suggestions welcome

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

From the Jade support I was able to confirm that WHERE and NULL are valid key words. 

But the only way to avoid the "Invalid Comparison of types error" was to compare it to another field that jade considered to be a date.

So what ended up giving me the employees who didn't have a termination date was to check if the termination date was after the start date.

[Enders]:

LOAD

    "code" as EndEmpCode,

    "usualName" as EndName,

    "position" as EndPosition,

    Date("terminationDate",'DD/MM/YYYY') as Date;

SQL SELECT

  "code",

    "usualName",

    "terminationDate",

    "position"

FROM HRPerson WHERE terminationDate>startDate;

View solution in original post

4 Replies
Chanty4u
MVP
MVP

is it required?   "

try this once

FROM HRPerson

WHERE terminationDate<>;

jonathandienst
Partner - Champion III
Partner - Champion III

Is expect you are looking to exclude null values. A comparison with a null value will always return false. The correct way in most databases is of the form:

Where terminationDate IS NOT NULL;

or

Where Not(terminationDate IS NULL);

I am not familiar with Jade, so you would have to check the documentation if neither of these work

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

From the Jade support I was able to confirm that WHERE and NULL are valid key words. 

But the only way to avoid the "Invalid Comparison of types error" was to compare it to another field that jade considered to be a date.

So what ended up giving me the employees who didn't have a termination date was to check if the termination date was after the start date.

[Enders]:

LOAD

    "code" as EndEmpCode,

    "usualName" as EndName,

    "position" as EndPosition,

    Date("terminationDate",'DD/MM/YYYY') as Date;

SQL SELECT

  "code",

    "usualName",

    "terminationDate",

    "position"

FROM HRPerson WHERE terminationDate>startDate;

SunilChauhan
Champion
Champion

use where with load statement not select as you are applying num. and i think num(terminationDate) intented for qlikview only

[Enders]:

LOAD

    "code" as EndEmpCode,

//     "oid" as EmpOID,

    "usualName" as EndName,

//     "Type" as EndType,

//     Basis as EndBasis,

    "position" as EndPosition,

    Date("terminationDate",'dd/MM/yyyy') as Date  WHERE Num(terminationDate)<>'';

SQL SELECT

  "code",

    "usualName",

    "terminationDate",

    "position"

FROM HRPerson;

Sunil Chauhan