Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gerhardl
Creator II
Creator II

SubStringCount with wild match

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?

1 Solution

Accepted Solutions
qlikviewwizard
Master II
Master II

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

Capture.PNG

View solution in original post

4 Replies
sunny_talwar

May be this

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

gerhardl
Creator II
Creator II
Author

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

qlikviewwizard
Master II
Master II

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

Capture.PNG

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