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):
Load … From "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
.png "Former Employee"){kind=icon}