4 Replies Latest reply: May 18, 2016 3:55 AM by Sunil Chauhan RSS

    ODBC connection - apply a WHERE on loading

    Neville Howard

      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

        • Re: ODBC connection - apply a WHERE on loading
          Chanty 4u

          is it required?   "

           

          try this once

           

          FROM HRPerson

          WHERE terminationDate<>;

          • Re: ODBC connection - apply a WHERE on loading
            Jonathan Dienst

            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

            • Re: ODBC connection - apply a WHERE on loading
              Neville Howard

              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;

                • Re: ODBC connection - apply a WHERE on loading
                  Sunil Chauhan

                  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;