topic Re: wildmatch function in QlikView

wildmatch function

QSense — Wed, 24 Apr 2013 08:49:01 GMT

Hi ,

I have a field it includes number and string text such as 1234 abcd,4321 desf

I want to use wildmatch function in order to get numbers

My code example is

if(WildMatch(Field,'*300914*','*120001031*')=1,'good','bad') but this code is working only for ' 300914'

it doesn not work both situation (300914 and 120001031)

Re: wildmatch function

Gysbert_Wassenaar — Wed, 24 Apr 2013 10:12:41 GMT

Try: if(WildMatch(Field,'*300914*','*120001031*'),'good','bad')

Wildmatch returns 1 if it matches 300914 and returns 2 if it matches 120001031

Re: wildmatch function

kuba_michalik — Wed, 24 Apr 2013 10:35:35 GMT

WildMatch (and any variant of Match) returns the position on which the result was matched. So in your case for 120001031 it returns 2.

If you want to use it for true/false comparisons, always use Match(...)<>0 or just simply Match() with no comparison operator (everything but 0 and null is treated as True).

Re: wildmatch function

Wed, 24 Apr 2013 10:57:31 GMT

You will have to split the expression using an or statement. Try something like...

if(WildMatch(Field,'*300914*')=1 or WildMatch(Field,'*120001031*')=1,'good','bad')

But that will only work for those two numbers. If this is not what you want please be clearer..

Re: wildmatch function

Anonymous — Wed, 24 Apr 2013 11:48:59 GMT

Try nested if condition as follows

If(WildMatch(Field,'*300914*')=1),'good',If(WildMatch(Field,'*120001031*')=1),'bad')

Regards

Revathy

Re: wildmatch function

Tue, 27 May 2014 23:06:25 GMT

Could also do:

pick(wildmatch(Field,'*300914*','*120001031*'),'good','bad'))

Re: wildmatch function

Anonymous — Fri, 17 Apr 2015 23:22:20 GMT

Please try using greater than equal (>=) instead of just equal (=):

if(WildMatch(Field,'*300914*','*120001031*')>=1,'good','bad')

Re: wildmatch function

Anonymous — Fri, 03 Mar 2017 00:46:54 GMT

I'm going to bump this - did you find a resolution? Unfortunately none of the suggestions have worked for me.

I have the same issue - wanting to find the number of clients who have both these products, (example) 'Car' & 'Boat'. Not one or the other - both.

I have as the wildmatch - if (WildMatch(Product, '*Car*','*Boat*')=1,'Combo',null), with the measure count(Client ID), and with the option of showing null values unchecked.

I have the problem that it's only returning the number of clients with Cars, not the number of clients with both a Car and a Boat.

In other words, say there's a 100 clients with Cars, and another 80 with Boats, and 50 have both a Car and a Boat, it's returning 100, not 50.

If I change it to '=2' it returns the number of boats, 80. And if I leave out the =1 or 2 altogether, it brings back the combined total, so 180.

I am using Qlik Sense Server, but the SQL theory/syntax should be the same.

Any help appreciated please.

Re: wildmatch function

Fri, 03 Mar 2017 03:38:48 GMT

Could you try some set analysis expressions like

=count({<[ClientID] = p({<[Products] = {A}>}) * p({<[Products] = {B}>}) >} distinct [ClientID])

Hope it helps!

Re: wildmatch function

Anonymous — Fri, 03 Mar 2017 03:45:20 GMT

Thanks Kiruthi.

I'm not really sure where this fits in with either a table or a pivot table sorry.

I was using the Wildmatch as a Row Dimension in a pivot table - where would your expression fit in please? I'm relatively new to Qlik.

Re: wildmatch function

Fri, 03 Mar 2017 04:01:51 GMT

Hi Richard,

You could use a straight or pivot table with clientId as dimension. Attached a sample application .

Hope it helps!

Re: wildmatch function

Anonymous — Thu, 09 Mar 2017 01:04:34 GMT

Thanks Kiruthi

Sorry, to clarify - where in your script do I specify 'Car' and 'Boat' please? I'm really not sure where this fits...

cheers

Re: wildmatch function

ffd — Mon, 20 Mar 2017 22:06:55 GMT

Hi Richard - I think Kiruthi's solution should work. To rephrase your question, if I understand correctly, you want to know how many rows with a value for client ID are associated with Product ~ "*Car*" and Product ~ "*Boat*".

To rephrase that again:

How many distinct clientID remain selectable (count)

if the set of all clientIDs is reduced to ( { < restrictions> }

the intersection of ( set modifier asterisk = intersection)

the set of clientIDs that are possible to select when Product contains Car (p for possible function)

and

the set of clientIDs possible to select when Product contains Boat.

I think you may need to use wildcard search in the set expressions, though, for instance p({<[Products] = {"*Car*"}>})

To read more about set modification, check this out: https://help.qlik.com/en-US/sense/3.2/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/set-modifiers.htm