topic Re: SubStringCount with wild match in QlikView

SubStringCount with wild match

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

I am trying to count the number of times a certain string appears within a single field, which can easily be done with SubStringCount.

But I want to count a string that always looks the same except for a number in the middle of it, so I need to do a wildmatch.

The string I'm looking for will be something like this:

"RuleSet 7: FAILED"

but the "7" can be any number (1 to 999).

if I do a wildmatch like this, it just returns a 0 or 1 (true or false):

Wildmatch(STATUS_DESC,'*RuleSet * FAILED*')

How do I count the NUMBER of times I get the "RuleSet *: FAILED" data string?

Re: SubStringCount with wild match

sunny_talwar — Mon, 09 Jul 2018 15:00:44 GMT

May be this

Sum(-WildMatch(STATUS_DESC, '*RuleSet * FAILED*'))

Re: SubStringCount with wild match

gerhardl — Wed, 11 Jul 2018 08:40:18 GMT

This doesn't work in the Load Script. Invalid expression

Re: SubStringCount with wild match

qlikviewwizard — Wed, 11 Jul 2018 09:41:48 GMT

Hi,

Try Like this.

DATA_Temp:

LOAD *,PurgeChar (STATUS_DESC,'0123456789' ) as purgechar INLINE [

STATUS_DESC

Aaa

RuleSet 7: FAILED

RuleSet 8: FAILED

RuleSet 9: FAILED

RuleSet 10: FAILED

RuleSet 11: FAILED

RuleSet 12: FAILED

RuleSet 13: FAILED

RuleSet 14: FAILED

RuleSet 15: FAILED

RuleSet 16: FAILED

RuleSet 17: FAILED

RuleSet 18: FAILED

RuleSet 19: FAILED

RuleSet 20: FAILED

RuleSet 21: FAILED

RuleSet 22: FAILED

RuleSet 23: FAILED

RuleSet 24: FAILED

RuleSet 25: FAILED

RuleSet 26: FAILED

];

Concatenate

LOAD *,1 as Flag Resident DATA_Temp

where WildMatch(STATUS_DESC,'*RuleSet*') or WildMatch(STATUS_DESC,'*FAILED*');

Re: SubStringCount with wild match

sunny_talwar — Wed, 11 Jul 2018 10:20:45 GMT

May be just do this

If(WildMatch(STATUS_DESC, '*RuleSet * FAILED*'), 1, 0) as Flag

This should give you 1 for everytime the condition is met, 0 otherwise. Then you can just Sum(Flag) to get the count