topic Re: Filtering a field in Set Analysis with '' in the string value in QlikView

Filtering a field in Set Analysis with '' in the string value

50ShadesOfSalty — Sat, 16 Nov 2024 19:09:09 GMT

Hello everyone,

I'm currently having an issue filtering a value that has '' in the string.

For example, consider the following example:

ID | NAME

1 | Mike

2 | Paul

3 | Robinson

4 | Owen 'The Flash'

5 | John 'The Cool'

When i try to: Sum( {< [NAME]={'Mike', 'Paul'} >}[Sales]), all works as it should.

Now, if i try to do Sum( {< [NAME]={'Owen 'The Flash''} >}[Sales]), i encounter an error because of the ' that are inside of the string value that i want to filter.

Also, here are some examples that i tried in order to overcome this issue:

'Owen '&chr(39)&'The Flash'&chr(39)

$(=chr(39)&'Owen '&chr(39)&'The Flash'&chr(39)&chr(39))

Any help for this issue is highly apreciated, as long as i'm having some troubles with this issue!

Thanks,

50ShadesOfSalty

Re: Filtering a field in Set Analysis with '' in the string value

veidlburkhard — Sun, 09 Feb 2020 16:40:32 GMT

Hi,

try

Sum( {< [NAME]={"Owen 'The Flash'"} >}[Sales])

This should work.

Regards

Burkhard

Re: Filtering a field in Set Analysis with '' in the string value

petter — Sun, 09 Feb 2020 18:23:34 GMT

If you still want to do a string literal match and not a string search then you can do it by escaping the character by having it twice like this:

Sum( {< [NAME]={'Owen ''The Flash'''} >}[Sales])

Notice that there are only single quotes in there - not double quotes although it might look like it...

Sum( {< [NAME]={"Owen 'The Flash'"} >}[Sales])

Will also work although this is a search and not a match - and it is case-insensitive whereas using single quotes will be case-sensitive...

Re: Filtering a field in Set Analysis with '' in the string value

50ShadesOfSalty — Mon, 10 Feb 2020 10:15:00 GMT

This actually worked!

Thank you!