Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Exists function

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Exists function

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.

6 Replies

Re: Exists function

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

roharoha
Valued Contributor III

Re: Exists function

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

MVP
MVP

Re: Exists function

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

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.

Not applicable

Re: Exists function

Thanks for help. Very clear problem solving.

oknotsen
Honored Contributor III

Re: Exists function

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post).

If not, please make clear what part of this topic you still need help with .

May you live in interesting times!