topic Re: Wildcard in Expression in QlikView

Wildcard in Expression

kevbrown — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm looking to create a bar chart based on an expression that will need a wildcard search. So I have a column called UPC, in which there are codes, like RM1234, AB1234, TF1234 etc. I need to pull out where the code = only RM. there is a further criteria to the expression in that I need to pull out only where there the Status field = 'Low', So i have the expression, Count(IF(UPC='RM*' and Status ='Low', '1')) but it's not working. The expression is ok but no data is pulling through.

Kev

Re: Wildcard in Expression

Anonymous — Wed, 08 Jun 2016 08:28:04 GMT

did you try with set Analysis?

count({<UPC={'RP*'},Status={'Low'}>}yourfield)

Re: Wildcard in Expression

tamilarasu — Wed, 08 Jun 2016 08:33:36 GMT

Hi Kevi,

You can try either below expression

Count(IF(Wildmatch(UPC, 'RM*') and Status ='Low', '1'))

Count(IF(UPC like 'RM*' and Status ='Low', '1'))

Re: Wildcard in Expression

Wed, 08 Jun 2016 08:36:17 GMT

This is a good solution that should solve the problem. If this selection of RM and Low is something you would use in multiple places would I create flags for it in the script instead; either to use in the set analysis or to count on ex

IF(UPC='RM*' and Status ='Low', 1,0) as _RMLow_Flag

and then in the front end just do a count(_RMLow_Flag). This also gives you an option to get a percentage of how many of your instances that are just RM Low which can come handy.

Good luck!

Re: Wildcard in Expression

Wed, 08 Jun 2016 08:36:40 GMT

Try

count({$<Status={"Low"},UPC={"RM*"}>}UPC)

Re: Wildcard in Expression

pho3nix90 — Wed, 08 Jun 2016 09:47:40 GMT

You could use the following:

Count(IF(WildMatch(UPC,"RM*") and Status ='Low', 1))

or use set analysis

Count({<UPC={"RP*"},Status={'Low'}>} 1)

See here when to use double quotes or single quotes

https://community.qlik.com/blogs/qlikviewdesignblog/2013/04/09/quoteology#comment-17692

Re: Wildcard in Expression

Kushal_Chawda — Wed, 08 Jun 2016 09:59:21 GMT

=Count({<UPC={"RM*"},Status={"Low"}>}1)

Count(if(wildmatch(UPC,'RM*') and Status='Low',1)

Count(if (UPC like 'RM*' and Status='Low',1)

I would suggest to use set analysis expression

Re: Wildcard in Expression

kevbrown — Wed, 08 Jun 2016 10:05:38 GMT

Superb, thanks

Re: Wildcard in Expression

Kushal_Chawda — Wed, 08 Jun 2016 10:09:22 GMT

Hi kevin,

It is better to use set analysis instead if conditions