Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event 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
stevedark
MVP & Luminary
MVP & Luminary

Hi Henric - thanks for this - another very useful post I can see myself pointing a number of people this way.

The one I find slightly counter-intutive is the GetCurrentField function.  As a function it feels like it should require single quotes (in the same was as NoOfRows and Peek) but actually square brackets are required when spaces are present.

The other thing I see people struggle with sometimes is putting a single quote into a variable, like this:

let vString = 'that's a problem';

I tend to fix this with a replace on an un-used character:

let vString = replace('that¬s fixed it', '¬', chr(39));

3,630 Views
Henric_Cronström

You're right that putting an apostrophe into a string is sometimes cumbersome. Your solution is a good one and I can only recommend to use the replace function here.

In your specific case, there is however a simpler solution:

   Set vString = "that's not a problem";

  

In other words: you can use other quotation marks in a Set statement.

HIC

3,630 Views
Or
Master
Master

Thanks, Henric - useful stuff, as always.

0 Likes
3,630 Views
swuehl
MVP
MVP

Thank you very much for your great series, Henric, also for your comments, containing almost every single time some useful piece of information.

Any chance that some of the stuff you and your collegues post in the design blog will find its way into the offical docs (Ref manual and HELP)?

My personal feeling is that there was not much work on these docs the past 2 years, and it would be great not only for starters with QV but for all of us to find a more detailed, correct description of the QV functionality where I think I should find it first place (though I started to archive your series anyway).

3,630 Views
preminqlik
Specialist II
Specialist II

Thanks for yur usefull informaton,good disscussion ,.as always

0 Likes
3,630 Views
rnt
Employee
Employee

Great blog post!

I can confirm that the official documentation (Reference manual and Help) will be updated and improved in the area of how to use quotation marks.

0 Likes
3,630 Views