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.
"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.
'*99', 'P1586', '?15*', '?17*', '*'
'Taxes', 'Premium Fuel', 'Fuel', 'Lubricant', 'Other')
The power of QlikView caching
4 days ago