Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

nelrhaffouli
New Contributor

How to exclude items when the title contain a specific string

Hello,

I would like to calculate the sum of items, but only when their title doesn't include a specific string.

for example, here is my list

Titlenumber
Banana20
Apple10
Apple Test15
Strawberry1
Mango testing2
test2

So basically what I want is to have only Banana + Apple +Strawberry and ignore anything that contains "test" in it.

It's just an example, my table got hundreds of lines.

I tried this

Sum({$<SubStringCount([Title], 'test')={0}>}[number])  but it doesn't seem to be working.

Any idea?

Thank you so much

Tags (1)
1 Solution

Accepted Solutions

Re: How to exclude items when the title contain a specific string

Seems to be working for me here

Capture.PNG

9 Replies

Re: How to exclude items when the title contain a specific string

May be this

Sum({<Title -= {'Test', 'test', 'TEST'}>}number)

nelrhaffouli
New Contributor

Re: How to exclude items when the title contain a specific string

Hello Sunny,

Thank you,

is there anyway to include all the possible syntax of "test", beacause I have also "testing" or "tester"

Thanks a lot,

Re: How to exclude items when the title contain a specific string

My bad, try this

Sum({<Title -= {'*Test*', '*test*', '*TEST*'}>}number)

nelrhaffouli
New Contributor

Re: How to exclude items when the title contain a specific string

This one didn't work, it returned to me all  the values, as '*' was actually considered as a character..

Re: How to exclude items when the title contain a specific string

Oh are you on the most recent version of QlikView or Qlik Sense? Try with double quotes instead of single quotes

Sum({<Title -= {"*Test*", "*test*", "*TEST*"}>}number)

nelrhaffouli
New Contributor

Re: How to exclude items when the title contain a specific string

Yes, I'm on Qlik Sense, and actually title = or title -= didn't work neither, it didn't include for example 'Mango Testing and "Apple Test" :-/

Re: How to exclude items when the title contain a specific string

"*Test*" is not excluding those two values? Really? Going to test it out now

Re: How to exclude items when the title contain a specific string

Seems to be working for me here

Capture.PNG

nelrhaffouli
New Contributor

Re: How to exclude items when the title contain a specific string

Thank you so much Sunny, I tried it again and it's working fine.  Perfect

Community Browser