Loading Script: WHERE & list of elements (IN)

[#1]

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!

[disqr_rm · Accepted solution]

try:

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

OR

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

OR

Match(.....)>0

[#3]

Thank you very much Rakesh !

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

Thanks 🙂

[johnw]

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.