topic Escaping single quotes in set expression field values in QlikView

Escaping single quotes in set expression field values

Thu, 01 Apr 2010 17:21:06 GMT

I have developed an application which caches selections in the form of a set expression.

It seems to work fine, except when I have a string value with a single quote ['] character in it.

It's possible to escape this character by changing it to two single quotes [''], as is the case with SQL, but this results in the value not being properly recognized.

In the end, the solution I came up with is to use the apostrophe character [`] as the string delimiter. This works fine - as long as my text has no apostrophes in it.

I was also thinking of using double quotes ["] but this invokes a search query which is not necessary, since I know the values.

Anyone know of any solutions?

Escaping single quotes in set expression field values

Thu, 01 Apr 2010 18:02:07 GMT

Hello,

I have a field N which contains the string "T's" (T apostrope s).

I can use this expression to sum up value V:

Sum({<N={$(='T'&chr(39)&'s')}>}V)

It is not a full solution to your question, but probably gives you an idea.

hth,
Thilo

Escaping single quotes in set expression field values

Thu, 01 Apr 2010 19:20:51 GMT

Thanks for the tip.

In spite of my best efforts, I have never been able to the get the dollar-expansion to work for me in set expressions. Furthermore, the documentation shows that you're supposed to write it as $(#=[expression]), but the examples I've seen omit the hash sign.

Maybe it has to do with the fact that the field I'm restricting values for is an AND field (although I fail to see how pre-processing a string value would make any difference).

Even if I could get this working, this seems a bit excessive given how most other formula and programming languages have simpler ways to escape characters.

Hopefully the product managers of QlikView will address this in their next release.

Escaping single quotes in set expression field values

johnw — Thu, 01 Apr 2010 19:23:55 GMT

As I recall, the # character forces the result to be a number. That's not typically necessary, and for text isn't desirable.

Escaping single quotes in set expression field values

Thu, 01 Apr 2010 19:33:04 GMT

Thanks for the pointer. Didn't realize that.

Escaping single quotes in set expression field values

Anonymous — Mon, 05 Apr 2010 08:12:51 GMT

Also, I don't think there's a good way to escape characters at the moment. Instead one has to work around it like you've done by using either double quotes if you want to escape a single quote etc.

Re: Escaping single quotes in set expression field values

Anonymous — Wed, 26 Sep 2018 01:21:06 GMT

It worked perfect! Thanks