Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading Script: WHERE & list of elements (IN)

Hello

I would like to know if it's feasible to use a IN syntax in the WHERE in the load script for QlikView.
Indeed, i would like to load only a few values and the

"WHERE Field =Value1 AND Field=Value2...."
seems to me much much more time (and resource) expensive for QlikVIew.

What i would like to know if we can, in the load script, tell qlikview to load using this syntax:

LOAD *

WHERE Field IN {'1,'2'}....

Thanks a lot for your futurs answers!

1 Solution

Accepted Solutions
disqr_rm
Partner - Specialist III
Partner - Specialist III

try:

WHERE pick( field, '1', '2', '3', '4' ) > 0

OR

len(pick( field, '1', '2', '3', '4' )) > 0

OR

Match(.....)>0

View solution in original post

3 Replies
disqr_rm
Partner - Specialist III
Partner - Specialist III

try:

WHERE pick( field, '1', '2', '3', '4' ) > 0

OR

len(pick( field, '1', '2', '3', '4' )) > 0

OR

Match(.....)>0

Not applicable
Author

Thank you very much Rakesh !

I've tested your options, for the project needs, the Match function is the best 🙂

Thanks 🙂

johnw
Champion III
Champion III

Since match() returns either 0 or the position of the match, this satisfies a simple true/false test without checking > 0. You can just say where match(field,value,value,value). But it is convenient in some cases to remember that match returns the number of the match, not just "true".

I believe that pick() would only work in some cases, as its job is to evaluate a number, and pick the value at that number in the following list. So the example pick(field,'1','2','3','4')>0 would verify that field was between 1 and 4. But so would pick(field,'22','hello','bob',9)>0. And if you wanted to verify that your number was either 22 or 5000, you'd be out of luck with pick(). You'd also be out of luck of you wanted to do this with a text field.

So generally speaking, I use match() as QlikView's version of IN.

And not that this was something you asked, but I find pick(match()) to be something I often use. Match returns a number, then pick says to use the value at that number's position. So for example, pick(1+match(field,'A','B'),field,'X','Y') would convert A to X, B to Y, and leave other values alone. This can be done in other ways, of course. It's just an example of the syntax.