In SQL, the “in” operator is commonly used to test if a value exists in a list of values. For example:
SELECT * FROM TABLE WHERE CODE IN (‘a’, ‘b’, ‘f’);
New QV developers often spend some time looking for QV’s equivalent of the “in” operator. It’s the match() function.
LOAD * WHERE match(CODE, ‘a’, ‘b’, ‘f’);
The match() function, and its siblings “mixmatch” and “wildmatch”. are documented in the “Conditional Functions” section of the Ref guide and the help:
match( s, expr1 [ , expr2, …exprN ] )
Compares the string s to a list of strings or string expressions. The result of the comparison is an integer indicating which of the comparison strings/expressions matched. If no match is found, 0 is returned. The match function performs a case sensitive comparison.
mixmatch() works just like match() except it does a case insensitive comparision.
wildmatch() is another form that can be particularly useful. wildmatch() allows (but does not require) the “?’ and “*” wildcard characters in the match arguments.
wildMatch(text, ‘*error*’)
will match:
“An error has occurred”
“Error processing account nnnn”
QV 8.5 provides a “like” operator that allows for testing against a single value with wildcards:
text like ‘*error*’
Wildmatch can test against multiple values:
wildMatch(text, ‘*error*’, ‘*warning*’)
The match() functions return a number indicating which of the comparison strings was found. You can use this index number nested in a pick function to do “wildcard mapping” as an alternative to a nested if() function.
pick(
wildmatch(PartNo,
‘*99’, ‘P1586’, ‘?15*’, ‘?17*’, ‘*’
),
‘Taxes’, ‘Premium Fuel’, ‘Fuel’, ‘Lubricant’, ‘Other’)
-Rob
hi,
i was looking for a “translation” of oracle IN function with the possibility of searching in another table
…
where COD in (select COD from table2)
but match doesn’t answer my question (The exist.qvw of ur wonderful cookbook doesnt work)
Thank u for ur help.
Amos
could the problem with exists.qvw be i work with 8.1 QV version?
Hi Amos,
exists() should work fine in 8.1. I suggest you post the specifics of your problem to the QlikCommunity Forum at http://qlikcommunity.com. I’m sure you’ll find some answers there.
-Rob
The comparison of qlikview constructs to sql queries enabled me to understand it better. Thank you!
Can wildmatch be used in set analysis?