Escaping single quotes in set expression field values

[#1]

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?

[#2]

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

[#3]

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.

[johnw]

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

[#5]

Thanks for the pointer. Didn't realize that.

[Anonymous]

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.

[Anonymous]

It worked perfect! Thanks