Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nrhoward
New Contributor

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

Tags (1)
1 Solution

Accepted Solutions
nrhoward
New Contributor

Re: ODBC connection - apply a WHERE on loading

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;

4 Replies
sureshqv
Esteemed Contributor III

Re: ODBC connection - apply a WHERE on loading

is it required?   "

try this once

FROM HRPerson

WHERE terminationDate<>;

MVP
MVP

Re: ODBC connection - apply a WHERE on loading

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

nrhoward
New Contributor

Re: ODBC connection - apply a WHERE on loading

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;

chauhans85
Esteemed Contributor

Re: ODBC connection - apply a WHERE on loading

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;