Saturday, October 25, 2008

The match() Function

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

5 comments:

ricciovolante@libero.it said...

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

ricciovolante@libero.it said...

could the problem with exists.qvw be i work with 8.1 QV version?

Rob Wunderlich said...

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

Anju said...

The comparison of qlikview constructs to sql queries enabled me to understand it better. Thank you!

Dan said...

Can wildmatch be used in set analysis?