Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Escape sequences

Escape sequences are a general technique to represent characters that are not possible to represent directly. In QlikView the need is mainly for quotation marks, but in the general case it could also be strange characters, tabs, and newlines. How this is handled in QlikView is explained in this post.

When writing a string in QlikView, it must usually be enclosed in single quotes. But what if you want to use a single quote inside the string? The following will not work:

     Set variable = 'This year's number';

The reason is that the apostrophe in the word year’s will be interpreted as the single quote that ends the string. So what should you do instead?

One solution that I have seen often in the community is to hard-code it using the Chr() function:

Let variable = 'This year' & Chr(39) & 's number' ;
Let variable = Replace( 'This year#s number', '#', Chr(39) ) ;

Both these work fine, but they are maybe not very elegant. Instead, I would suggest one of the following methods. First, you can often use a different delimiter:

Set variable = [This year's number];

As you can see, the Set statement can also use square brackets (or double quotes) as delimiters. Hence, if you just use a delimiter that is different from what you have in the string, it will work. The same is true if you need to load from a file with a name that contains single quotes or square brackets. Just make sure you quote it using double quotes (which is a character that shouldn't exist in file names):

LoadFrom "This year's numbers [3].xlsx"

But there is a second way this problem can be solved. An escape sequence:

Let variable = 'This year''s number';

The Let statement is different from the Set statement in that you must use single quotes as delimiter for literals. So you need to use an escape sequence instead: Just write the single quote twice, and the two characters will not be interpreted as a string delimiter, but instead as a single instance of the character itself. The same method can be used in other places also, e.g. in Set Analysis. The following expression is a correct one picking out the records from Robert's unit:

     Sum({1<Unit={'Robert''s unit'}>} Amount)

An escape sequence can be used for double quotes and square brackets, too. So if you have a field name that contains double quotes, for instance Name”5, you can load it either by using square brackets or by escaping the double quote:

[Name"5] as Field1,
"Name""5" as Field2,

If the field name contains square brackets, e.g. a field called Name[5], only the right bracket needs to be escaped. Such a field can be loaded in either of the two following ways:

"Name[5]" as Field1,
[Name[5]]] as Field2,

With this, I hope you got some ideas about how to deal with odd characters and strangely named fields.

HIC

 

Further reading related to this topic:

QlikView Quoteology

19 Comments
Partner
Partner

I recall you saying @qonnections2015 that single quotes in Set Analysis is a "bug" and double quotes should be used instead.

0 Likes
3,935 Views
Luminary
Luminary

I thought that bug was just when you're doing search type arguments in Set Analysis?

0 Likes
3,935 Views
Partner
Partner

See HIC's reply in the following thread;

QlikView Quoteology

Capture.PNG

So you are right when it comes to the exact sentence (case sensitive) <<Robert's unit>> but i think that we all should start using double quotes in set analysis as a general rule and only use single quotes when the upper/lower case matters.

0 Likes
3,935 Views

To be absolutely clear - it should work like this:

  • Single quotes in Set Analysis = Case sensitive match with single field value. Wild cards are not possible.
  • Double quotes in Set Analysis = Search, e.g. a case insensitive wildcard search

The bug is that both types of quotes are today interpreted as (case insensitive) searches where you can use wild cards.

Should you start to always use double quotes? No, not if you want a literal match with a string (no wildcards). Then you can continue to use single quotes.

HIC

0 Likes
3,935 Views
Partner
Partner

Is there maybe an elegant method to escape $(), so it's possible to avoid macro expansion at variable assignment time?

0 Likes
3,935 Views

No, unfortunately not. You will have to use one of the non-elegant methods...

But when would you need it? I can only think of a case where you use an equal sign to make the variable dynamic, e.g.

     Let variable = '=$(otherVariable)';

and here you don't need the dollar expansion. You can write this as

     Let variable = '=otherVariable';

HIC

3,935 Views
Partner
Partner

Typically, when I would like to store a formula in the variable, and make a part of this formula dynamic based on contents of some other variable, or a calculation (the $(=...) type of expansion). Thankfully, this is no issue when assigning the variable contents from an external store (which is the saner option when there is plenty of them, anyway), but it still would be nice to be able to do it cleanly in script

0 Likes
3,935 Views

There is a method that is a bit more elegant than the default ones:

LET vVariable = 'Keep for 2nd stage expansion $' & '(vStuff)';

0 Likes
3,935 Views

I didn't realise that these days you needed quotes on the right hand side of a SET statement? What's wrong with

SET variable = This year's number;

The only thing I can see that may be 'unwanted' is that making use of its unquoted value may push you towards $-sign substitution instead of just dropping in the variable name.

0 Likes
3,935 Views

Normally you don't need to quote in a Set statement. But in this case you do. Otherwise the parser gets lost when it encounters the single quote. Try it, and you'll see that the variable value contains a lot more than you want...

HIC

3,935 Views

You're right Henric (as always). It was a bit difficult to detect, as I only had the terminating semicolon getting stored with the string.

Again misled by the QV documentation/help that says, The let statement, in opposition to the set statement, evaluates the expression on the right side of the '=' (indeed, from the LET help) I thought not evaluating the right hand side meant not deciding whether this is a string or not. Just look for the terminating semicolon. Sorry. QV is still very capable at dodging expectations

3,935 Views

This is a very creative example of QV script parser behavior with respect to quoting:

SET vVariable = ]/*[;

This syntax is perfectly valid. It will only store the initial closing bracket in vVariable. The opening bracket will disappear due to the opening comment, but the semicolon will function as expected (?). It will terminate the current statement. All subsequent (non-wacky) statements will execute as expected.

Not true: the comment will wipe out all remaining script text (or until it encounters its counterpart). The syntax checker however doesn't obey the same rules as the script engine, it seems.

0 Likes
3,935 Views
Not applicable

Thank you!!! I have got to remember this one - so helpful....

0 Likes
3,935 Views
Luminary
Luminary

Thank you HIC. Very useful to know. I'm glad that extra quote used as escape character is no stranger to the QV syntax parser.

0 Likes
3,935 Views
Not applicable

Thank you for sharing the article

0 Likes
3,935 Views
kkkumar82
Valued Contributor III

Hi HIC,

replace(QuarterYear,''', 'FY') I had this situation so I replaced the second argument with chr(39) as

below replace(QuarterYear, chr(39) , 'FY'). My ask is Is there a way in the above to still use single quote as second arguments.

Hope I am clear

Thanks

Kiran Kumar

0 Likes
3,935 Views

I would have expected the following to work:

   Replace(QuarterYear,'''', 'FY')

... but it doesn't. I'll investigate.

Bottom line is that right now I don't see any other way than to use Chr(39)

HIC

0 Likes
3,935 Views
saran7de
Contributor III

Hi HIC,

Is there a way to escape \n or \t in a text?

I could not find any solution, other than removing it.

Escape \n

0 Likes
3,935 Views
m_r_de_wit
New Contributor II

Hi all,

Lot of useful information concernig quotation, but not my quotation problem.

I've a variable containing a text starting with a single quote (chr(39)).

I'm trying to use this variable (within $()) as literal in a Load statement, but as literals should come in single quotes it doesn't work.

Here is my code:

The QV parser inserts Escape sequence trying to solve the problem.

My variable vFile contains a single quote:

But the error comes with following debug information, the literal is starting with a triple single quote:

According to previous information, I would assume that the second and third quote would resolve as Escape sequence, but that doesn't work. My guess is doesn't work because it's at the beginning of the literal.

My simple solution is to replace the single quote by quotation mark, that works.

However my style is 'perfect and simple' solutions: so I'm still looking for a better wat to solve this.

Anyone a simple and perfect solution?

0 Likes
3,935 Views