Script Expressions & Quotation marks in Qlikview

Script Expressions & Quotation marks in Qlikview

In the script, the name of the data source, the name of the table and the name of the field, included in the logic are defined. Furthermore, the fields in the access rights definition are defined in the script. The script consists of a number of statements that are executed consecutively.

Script Expressions:

Expressions can be used in both the LOAD statement and the SELECT statement. SELECT statement interpreted by the ODBC driver and not by QlikView. Expressions consist of functions, fields and operators, combined in a syntax.

Learn how to use QlikView, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free QlikView Training Demo!

QlikView chart expressions are used in the front-end chart objects in QlikView, but script expressions are used in the QlikView script itself. Script expressions consist of scripting functions, fields, and operators. The most used script functions consist of the aggregation functions and inter-record functions. You may also see statistical functions and financial functions (but these functions are more common in the front-end chart expressions). Other common functions include counter, date and time, conditional, numeric, interpretation, and string functions.

All expressions in a QlikView script return a number and/or a string, whichever is appropriate. Logical functions and operators return 0 for False and -1 for True. Number to string conversions and vice versa are implicit. Logical operators and functions interpret 0 as False and all else as True.

Frequently Asked QlikView Interview Questions & Answers

Script expressions consist of scripting functions, fields, and operators. The most used script functions consist of the aggregation functions and inter-record functions.

A note about QlikView terminology: the term fields correspond to data columns in a table, which contain records (which correspond to rows in a table). Fields and records are incorporated into files (also known as QVDs). Operators are categorized as numeric operators, string operators, logical operators, relational operators, and bit operators.

Script expressions return either a number, a string, or both. Logical script expressions (functions) and operators return either zero (0) for false or (-1) for true. Expressions can also convert strings to numbers and numbers to strings.

Quotation marks:

The use of quotation marks in the QlikView syntax language can be tricky, and depends on whether you are referring fields, values, or variables. Usually, quotations are used for field values inside a Load statement. Note that the use of quotation marks inside Select statements may vary, depending on the type of the data source and ODBC/ OLE DB driver (Oracle, MySQL, DB2 and so on may interpret quotation marks differently). There are some basic rules to follow for using quotation marks in QlikView Load statements:

• Single quotes are used to refer literal values. Literal values are strings (of numbers or text) that will be used as field values.

• Source data field names inside Load statements commonly use double quotes. The field names are usually found to the left of the AS specifier in a Load statement.

•  Square brackets can be used instead of double quotes.

You can use quotation marks in script statements in a number of different ways.

In SELECT statements

For a SELECT statement interpreted by the ODBC driver, it may be slightly different. Usually, you should use the straight double quotation marks (ALT + 0034) for field and table names, and the straight single quotation marks (ALT + 0039) for literals, and avoid using grave accents. However, some ODBC drivers not only accept grave accents, as quotation marks, but also prefer them. In such cases, the generated SELECT statements contain grave accent quotation marks.

Outside LOAD statements

Outside a LOAD statement, in places where QlikView expects an expression, double quotation marks denotes a variable reference and not a field reference. If you use double quotation marks, the enclosed string will be interpreted as a variable and the value of the variable will be used.

Out-of-context field references and table references

Some script functions refer to fields that have already been created, or are in the output of a LOAD statement, for example Exists () and Peek (). These field references are called out-of-context field references, as opposed to source field references that refer to fields that are in context, that is, in the input table of the LOAD statement. Out-of-context field references and table references should be regarded as literals and therefore need single quotation marks.

Using single quote characters in a string

If you need to include single quote characters in a string, you can use an extra single quote as an escape character.

 A common mistake that developers make is when using a field name they incorporate a character such as a space or dash. In this case, one must use a double quote or bracket instead of space.

For example, if you have a data source field name with a space in it called “Client ID” then
Load Client ID
will cause a script error since QlikView expects an AS specifier or a comma after the word Client. The correct syntax is to use brackets or double quotes, such as:
Load [Client ID]
Or:
Load “Client ID”
What happens if you use a single quote? If you write this in your script:
Load ‘Client ID’
The text string Client ID will be interpreted by QlikView as a literal field value.

Related Article: Snowflake and Star Schema in Qlikview

When using quotation marks outside a Load statement, double quotes signal QlikView to look for a variable, not a source data field name. Outside of a Load statement, the string inside double quotes will be interpreted as a variable reference, and the value of the variable (if it exists) will be used.

Out-of-context field references (generated as a result of a Load statement) and table references such as the parameters in Exists, NoOfRows () and Peek (), are literal values and need single quotes.

QlikView also allows use of any of the quotation marks or brackets in many places, such as:

• The Set statement definitions (to the right of the equals sign)

• Aliases in the Load statement (to the right of AS)

• File names, URLs, or external table names

• Definitions regarding inline tables

• The first parameter of Peek () or Exists () inside Load statements

Master calendar Placement:

A Master Calendar is simply a table that contains a time period and time attributes that you define, linked to your existing data. If you are unable to analyze data by different time dimensions using your existing data set, or have noticed time gaps when create charts and visualizations – the Master Calendar will come in handy.

One script concept that must be considerate about is master calendars. They are special time-period tables that are linked to the fact tables. A master calendar is powerful in fact tables because it does not rely on the dates in the fact table itself as there may be dates for which there is no data. For instance, there may be days on which no goods are sold, and there will be no date available for selection if no data exists.

The master calendar is nothing but built out full calendar, usually by looking (peeking) at the minimum transaction date in the fact table, then auto populating dates for a range ending at the last transaction date (or the current date). The master calendar is typically the first or second tab in a script, and should ideally be located on its own tab, for easy reference and editing.

Please be aware that the Master Calendar can be created in a few different ways using Qlik scripting. It can also be made more efficient using specific methods and functions. Master calendars can be tricky to code, but there are certain third-party providers of master calendar script creation utilities.