Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
EXISTS is a Qlik function and so can only be used within a Qlik Load statement and not within a SQL SELECT.
I would do an inner join at sql level or use where with fields at sql level
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.
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.
Thanks for help. Very clear problem solving.
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 .