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.




Further reading related to this topic:

QlikView Quoteology