Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
Qlik Insider - Maximize your investment, Join this webinar, May 26th. Register Today
Henric_Cronström

In all programming environments there is a need for quotation marks, and QlikView is no exception. But which symbol should you use? " ", [ ], ` ` or ' ' ? This post will try to explain the differences between the different quotation marks.

When creating the script or an expression in QlikView, you need to reference fields, explicit values and variables. To do this correctly, you sometimes need to write the string inside a pair of quotation marks. One common case is when a field name contains a symbol that prevents QlikView from parsing it correctly, like a space or a minus sign.

For example, if you have a field called “Unit Cost”, then
     Load Unit Cost
will cause a syntax error since QlikView expects an "as" or a comma after the word "Unit".

If you instead write
     Load [Unit Cost]
QlikView will load the field “Unit Cost”. Finally, if you write
     Load 'Unit Cost'
QlikView will load the text string "Unit Cost" as field value. Hence, it is important that you choose the correct quotation mark.

So, what are the rules? Which quote should I use? Single? Double? Square brackets?

There are three basic rules:Image1.png

  1. Single quotes are used to quote literals, e.g. strings that should be used as field values.
  2. Double quotes are used to quote source field references, i.e. names of fields. (In formulae, or to the left of the “as” inside a Load statement.)
  3. Double quotes can always be substituted by square brackets or by grave accents.

 

With these three rules, most cases are covered. However, they don’t cover everything, so I'll continue:

  • In the script, but outside a Load statement, double quotes denote a variable reference and not a field reference. If double quotes are used, the enclosed string will be interpreted as a variable and the value of the variable will be used.

 

A general rule in QlikView is that field references inside a Load must refer to the fields in the input table – the source of the Load statement. They are source field references or in-context field references. Aliases and fields that are created in the Load cannot be referred since they do not exist in the source. There are however a couple of exceptions: the functions Peek() and Exists(). The first parameters of these functions refer to fields that either have already been created or are in the output of the Load. These are out-of-context field references.

  • Out-of-context field references and table references, e.g. the parameters in NoOfRows() and Peek(), should be regarded as literals and therefore need single quotes.
  • Finally, in many places you are free to use any of the four quotation methods, e.g.
    • Inside a Load statement, to the right of the “as”
    • In places where QlikView expects a file name, a URL or a table name
    • Defining the beginning and end of an inline table
    • For the first parameter of Peek() or Exists() when used inside a Load

 

I have deliberately chosen not to say anything about SELECT statements. The reason is that the rules depend on which database and which ODBC/OLEDB you have. But usually, rules 1-3 apply there also.

With this, I hope that the QlikView quoteology is a little clearer.

HIC

 

Further reading related to this topic:

Escape sequences

23 Comments
Marcio_Campestrini
Specialist
Specialist

HIC, thanks for share this post.

0 Likes
2,454 Views
Not applicable

Henric, thanks for this, as it explains some things, but I have a different issue with quotation marks. When I import a "Note" field from our CRM system (Goldmine), the opening and closing quotation marks get changed to "“" and "”" similar problems exist with apostrophes, etc.

I tried to do a replace function in my LOAD statement (e.g. REPLACE(CANote,'“',chr(34)) AS "Notes"), and that worked, but obviously only for the one value. If I tried to chain Replace statements, e.g. REPLACE(CANote,'“',chr(34)) AS C1Note, REPLACE(C1Note,'”',chr(34)) AS C2Note, etc., I got an error message "Field not found C1Note" (which, frankly, I expected).

I put the statements into a macro, using correct VBS syntax, and they compile and execute (or so it seems) without error, but  there are no changes made, not even to the first value.

Finally, I tried nesting REPLACE statements in my LOAD statement, e.g.

REPLACE(REPLACE(CANote,'“',chr(34)),'”',chr(34)) AS Note, and that worked, but I have at

least two other values I want to apply a similar fix to. I'm worried that 1) the code looks clunky, 2) there will be a performance hit, and 3) it will be difficult to maintain.

Is there a better way to accomplish this task?

0 Likes
2,454 Views
apg
Contributor
Contributor

Henric, what are the rules for escaping special characters for these different quotation options, for example, what if the field name in Load statement contains square bracket, or double quotation mark o both?

0 Likes
2,404 Views
Henric_Cronström

It should be possible to write the quoting symbol twice. This will be interpreted as an escape sequence for that character. For example:

    Set Variable = 'abc''def';

will assign the value    abc'def

Also,

    Load [Field[1]]] as Field_1

will load a field named    Field[1]

However, I am aware that there are some bugs here, so the string is not always correctly interpreted.

HIC

2,404 Views
Not applicable

Thx,

So, if I need a case sensitive modifier, I need to go like if and match ?

0 Likes
2,404 Views
Henric_Cronström

Correct.

0 Likes
2,404 Views
Not applicable

Hi Hic! I've Read all your documents, all equally interesting, just one question, now with QV 11.2 SR12 and soon we'll get QV 12 and also including Qlik Sense, I'd think that some of the rules from this article are gonna change, right?

0 Likes
2,404 Views
Henric_Cronström

No, the principles behind these rules will not change. The rules might be extended in the future though, for example: Allowing nested quotes, and allowing acute accent as quoting symbol.

Yes, some changes - bug fixes and removal of inconsistencies - will be introduced. (I am not sure the fixes will make it to the first coming versions though...)

HIC

0 Likes
2,404 Views
Oleg_Troyansky

Hi Henric!

very useful, as usual. If I may add my $0.02, in the example that Steve Dark brought up, with the single quote within the string, - in a simple case like this you can even omit all the surrounding quotes, like this:

set test = That's not a problem either;

the text to the right of the equal sign will get assigned to the variable, even with the single quote in the middle.

Oleg Troyansky

2,456 Views
Henric_Cronström

Oleg

Unfortunately that doesn't work - although I admit that it probably should... A single single-quote inside the string will make the parser lose track.

HIC

0 Likes
2,456 Views