Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 -
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
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;
is it required? "
try this once
FROM HRPerson
WHERE terminationDate<>;
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
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;
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;