SubStringCount with wild match

[gerhardl]

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?

[sunny_talwar]

May be this

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

[gerhardl]

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

[qlikviewwizard · Accepted solution]

Hi,

Try Like this.

DATA_Temp:

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

STATUS_DESC

Aaa

bd

n

Ad

bd

cd

Aaa

ba

mm

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*');

[sunny_talwar]

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