Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
swuehl
MVP
MVP

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.

View solution in original post

6 Replies
Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

swuehl
MVP
MVP

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.

rubenmarin

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
Author

Thanks for help. Very clear problem solving.

oknotsen
Master III
Master III

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!