topic simple scripting question for replicating SQL in() in QlikView

simple scripting question for replicating SQL in()

Fri, 10 Dec 2010 15:31:10 GMT

Hi all, just a quick quesiton, hoping someone can help, is there a way to search in expressions for multiple items as you would in SQL,

i.e when I load data I can say SELECT FIELD1,FIELD2 from SQL.db WHERE FIELD1 IN (1,2,3,4) however once you get into a document I only appear to be able to use if(Field1 =1 or Field1=2 or Field1=3 or Field1=4, Field1) is there not a way to clean this up?? I have tried using If (Field1=(1|2|3|4),FIELD1) but this also doesn't work. Any ideas?

Thanks

Ben

simple scripting question for replicating SQL in()

Fri, 10 Dec 2010 15:42:43 GMT

You could try Index(). WHERE Index('1,2,3,4', FIELD1)>0.

Index returns the position of the second paramater in the first. So if Index()>0, then the second parameter is In the first.

Findoneof() and Substringcount() are a couple of others that work, but I usually stick with Index().

simple scripting question for replicating SQL in()

Fri, 10 Dec 2010 15:56:50 GMT

This is good, closer than I have got before, but its not quite what I'm after, you can't use wildcards with index can you?

i.e. FIELD1 In('%1%') would pick up anything 1-22222 1-333 etc really after something with this functionality as well, unless there is a way to use wildcards with index?

simple scripting question for replicating SQL in()

Fri, 10 Dec 2010 17:50:42 GMT

Then you may want to switch it around: Index(FIELD1, 1). That should hit on 1-222222, 1-333, 867-5309-1, etc.

If you have more than one, Index(FIELD1, 1)+Index(FIELD1, 2)>0. If 1 or 2 appears in FIELD1, then you return true.

Look into Wildmatch(). Wildmatch(FIELD1, '*1*', 'Hello*'). It returns the parameter number where it found the match or zero for no match.