6 Replies Latest reply: May 1, 2017 8:12 AM by Onno van Knotsenburg RSS

    Exists function

    Vytautas Paurys

      I tries to load records where i06_op_tip equal 51 or 52. When i trie to use function EXISTS i get connector reply error, when I USE WHERE ... OR it works fine. What is wrong ?

       

      Type1:

        LOAD * INLINE [

        I06_OP_TIP

        51

        52

        ];

       

      Test:

      LOAD

        [I06_OP_TIP],

        [I06_DOK_NR],

        [I06_OP_DATA];

      SQL SELECT "I06_OP_TIP",

        "I06_DOK_NR",

        "I06_OP_DATA"

          FROM "ORBITA"."dbo"."I06_PARH"

      WHERE EXISTS (I06_OP_TIP); // ERROR

      // WHERE [I06_OP_TIP] = '51' OR [I06_OP_TIP] = '52'; // THIS WORKS

        • Re: Exists function
          Bill Markham

          EXISTS is a Qlik function and so can only be used within a Qlik Load statement and not within a SQL SELECT.

          • Re: Exists function
            Robin Hausdörfer

            I would do an inner join at sql level or use where with fields at sql level

            • Re: Exists function
              Stefan Wühl

              In SQL, you can use WHERE ... IN (...) to shortcut your working WHERE clause with multiple OR conditions:

               

              SQL SELECT "I06_OP_TIP",

                "I06_DOK_NR",

                "I06_OP_DATA"

                  FROM "ORBITA"."dbo"."I06_PARH"

              WHERE "I06_OP_TIP" IN (51,52);

               

              You can also use a WHERE Exists() as part of your Qlik preceding LOAD statement:

               

              Test:

              LOAD

                [I06_OP_TIP],

                [I06_DOK_NR],

                [I06_OP_DATA]

              WHERE EXISTS([I06_OP_TIP]); //or other Qlik filter like  WHERE MATCH([I06_OP_TIP],51,52)

              SQL SELECT "I06_OP_TIP",

                "I06_DOK_NR",

                "I06_OP_DATA"

                  FROM "ORBITA"."dbo"."I06_PARH"

              ;

               

              But the second option will filter the records on Qlik side, not on the source DBMS, hence it's probably better performing to use first option.

              • Re: Exists function
                Ruben Marin

                Hi, you can also use a variable to centralize the management:

                SET vTypes = 51,52;

                 

                LOAD

                  [I06_OP_TIP],

                  [I06_DOK_NR],

                  [I06_OP_DATA];

                SQL SELECT "I06_OP_TIP",

                  "I06_DOK_NR",

                  "I06_OP_DATA"

                    FROM "ORBITA"."dbo"."I06_PARH"

                WHERE "I06_OP_TIP" IN ($(vTypes));

                 

                You can use use the Exists() function in the LOAD statement but there is no reason to retrieve all data from database to only keep a couple of types.