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

How to exclude items when the title contain a specific string

Anonymous — Wed, 29 Nov 2017 03:43:16 GMT

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

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

sunny_talwar — Wed, 29 Nov 2017 03:55:42 GMT

May be this

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

Anonymous — Wed, 29 Nov 2017 03:58:20 GMT

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,

sunny_talwar — Wed, 29 Nov 2017 04:00:22 GMT

My bad, try this

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

Anonymous — Wed, 29 Nov 2017 04:01:37 GMT

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

sunny_talwar — Wed, 29 Nov 2017 04:02:47 GMT

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)

Anonymous — Wed, 29 Nov 2017 04:05:42 GMT

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" 😕

sunny_talwar — Wed, 29 Nov 2017 04:07:15 GMT

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

sunny_talwar — Wed, 29 Nov 2017 04:14:41 GMT

Seems to be working for me here

Anonymous — Wed, 29 Nov 2017 04:15:51 GMT

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