Qlik Sense Script Syntax – Backus-Naur Formalism

1. Objective

In our last session, we discussed Qlik Sense Visualization Expression. Today, we will see Qlik Sense Script Syntax. In this lesson, we are going to learn about how data tables are loaded or written in the data load script of Qlik Sense. Also, we will start with understanding the standard script notation (BNF) which is followed to write codes in the script. Moreover, we will see what types of script statements and keywords are used in the script and how to the script expression is written.

So, let’s start the Qlik Sense Script Syntax Tutorial.

Qlik Sense Script Syntax – Backus-Naur Formalism

2. What is Qlik Sense Script Syntax?

In a data load script, various operations are performed for which script statements and keywords are required. The script code should be written in such a format or notation which is correct and readable for the machine. The script statements are executed consecutively if the code syntax is error free. You must always remember to end a script statement or each code line with a semicolon ‘;’ The operations performed through a script are, establishing a data connection, loading a data table, loading field names, and values, etc.

Recommended Reading – Qlik Sense Logical Functions

A certain initial piece of code is generated upon creating of a new file, setting up all the basic properties like number interpretation variables, formats etc. The two main keywords used for loading data are LOAD and SELECT. Under the LOAD statement, you can use expressions and functions to manipulate data as you like.

3. What is Backus-Naur Formalism?

Backus-Naur Form is a metalanguage. Metalanguages are the languages which are used to explain other natural or computer languages. Any metalanguage follows a specific notation technique or meta syntax through which rules of a language are defined and explained. In the world of computing, there are several widely used metalanguages are Backus-Naur Form (BNF), Extended Backus-Naur Form (EBNF), Augmented Backus-Naur Form (ABNF).

Formulating a language for computers in a well-defined and formatted structure is very important for the language to be properly converted into a machine language during execution.

Let’s discuss Qlik Sense Mapping function

The Backus-Naur Form is designed in a specific way with a set of derivation rules expressed as,

<symbol> ::= __expression__

Every BNF statement follows this rule, where the Symbol is a value defined using ‘::=’ sign and the __expression__ are the possible and valid variables that can taken up for the defined value. Let us understand this by dividing the above statement into a right half and a left half. The right half has a Non-terminal value and the left half has Terminal values.

a. Non-Terminals – The Non-terminals are syntactic or lexical entities that are used to define a symbol to be used in a language abiding the formal grammar rules. These are always enclosed in <>.

b. Terminals – The Terminals are symbols/values that are the probable outcomes of the defined (non-terminal entity/symbol) as per the rule. These values are not susceptible to change by the rule.

In an expression, let’s suppose that we are defining an entity <digit> then on the right side of the expression will be all the terminal values i.e. the possible values that can be taken up by the system as Digits like 0,1,2,3.. etc.

Script Syntax – Backus Naur Formalism

The values or symbol sequences defined on the right are separated by “|”, in case of more than one values. A Non-Terminal can also be defined in or replaced by one or more Non-Terminals on the right side. For instance, if after defining <digits> we want to define <integer> we can do it by creating a rule so that integer is replaced by one or more digits.

Have a look at Qlik Sense Null Functions

<integer> ::= <digit> | <digit><digit>

Here, both <integer> and <digit> are Non-Terminals and will give an output like,

<integer> ::= 5
<integer> ::= 86

4. Script Statements and Keywords

The script statements and keywords are the words or characters whose purpose or operation is pre-defined in the script. There is a total of three types of script statements, i.e. script regular statements, script control statements, and script prefixes.

a. The script regular statements are used to manipulate data in some general ways. You can apply these statements over numerous code lines. You must always end a script regular statement with a semicolon ‘;’. Given below, are all the script regular functions used in Qlik Sense script.

• Alias                                        
• AutoNumber
• Binary
• Comment field
• Comment table
• Connect
• Declare
• Derive
• Direct Query
• Directory
• Disconnect
• Drop field
• Drop table
• Execute
• FlushLog
• Force
• Load
• Let
• Loosen Table
• Map
• NullAsNull
• NullAsValue
• Qualify
• Rem
• Rename field
• Rename table
• Search
• Section
• Select
• Set
• Sleep
• SQL
• SQLColumns
• SQLTables
• SQLTypes
• Star
• Store
• Tag
• Trace
• Unmap
• Unqualify
• Untag

b. The script control statements are responsible for managing the execution flow of the script statement. That is, they decide which statement to execute before the other. Such statements should be kept under a script line and be eliminated using the semicolon or end-of-line. Given below are some of the commonly used script control statements in Qlik Sense.

Recommended Reading – Qlik Sense Line Chart

• Call
• Do..loop
• Exit script
• For..next
• For each..next
• If..then..elseif..else..end if
• Sub..end sub
• Switch..case..default..end switch

c. The script prefixes are used before script statements. Although these prefixes can only be used with script regular statements and as a suffix (when and unless) for some control statements. Given below are the prefixes used in Qlik Sense script statements.

• Add
• Buffer
• Concatenate
• Crosstable
• First
• Generic
• Hierarchy
• HierarchyBelongsTo
• Inner
• IntervalMatch
• Join
• Keep
• Left
• Mapping
• NoConcatenate
• Outer
• Replace
• Right
• Sample
• Semantic
• Unless
• When

5. Variables in Data Load Editor

The variables are like storage places or containers for numeric, alphanumeric or string values. A variable is defined in the beginning and can change its value accordingly which will reflect in all the places where that variable is used in the script. The variables are defined either in the variable overview section or in the script in the data load editor. You can define a variable and assign a value to it using the Let and Set statements in the data load script.

You must read Qlik Sense Conditional Functions

Whenever you assign a value to a variable, then upon script execution, that value is shown as a final result. But, if you use a ‘=’ sign, then the value of the variable is evaluated as an expression and the result of evaluation is returned. Variable values are evaluated under a formula and a result is returned. Variables are used when you want to use the same value or the same type of string or text many times within a string, for example, you can store a file path in a variable and use it later anywhere in the string.

i. Defining a variable

set variablename = string

A variable is defined by using two statements Set and Let.

set variablename = string

That is, set is used to assign a string value to a variable. For example,

Set name = Chris;
or
let variable = expression

Let is used to assign a numeric value or a value which can be used in calculations to a variable. For example,

let Days = Count(Today()); // returns the total number of entries done in the field ‘Today’ and assign the value in the variable Day.

ii. Variable calculation

The variable calculation takes place when you define variables using the Let statement and the functions you use in the statements. To understand it better, look at the sample data given below. Using this data, we will perform calculations.

LOAD * INLINE [
   Customer, Sales
   A, 150
   A, 200
   B, 240
   B, 230
   C, 410
   C, 330
];

Now, we will calculate two different things here, one will calculate the sum of the sales as per customer, i.e. individually for customers A, B, and C. The second statement will evaluate the total sales of all the customers.

Let cSales  = 'Sum(Sales)' ;
Let tSales  = '=Sum(Sales)' ;

You must read – Qlik Sense Mathematical Functions

In order to get the result of the evaluation in the table, we will use dollar signs in front of each Sum(Sales) statement like =$(cSales) and =$(tSales). By doing so, the variable is calculated and then expanded, and the result of the evaluation is return. If you do not use the dollar sign, then only strings will be returned by the statement.

If you wish to delete a variable, then you can do so by deleting it from the variable overview.

6. Qlik Sense Script Expressions

The script expressions are a piece of code which contains a combination of functions, fields, and operators written in the standard notation or syntax. During script execution, the elements of the script expression are evaluated, and the result which is returned as a string or number. If the script expression contains a logical function, then the result will be 0 for False, or -1 for True.

Expression ::=(constant | fieldref | operator1 expression | expression operator2 expression | function | ( expression ))

Where, constant can be a string or a number enclosed in single quotation marks. The string can be text, date or time, whereas, a number can be n digits long, but it must not be separated by a decimal or thousands separator.

Fieldref is the field name of the table that will be loaded.

Operator1 is a unary operator which will work on the expression to its right.

Operator2 is a binary operator which works on the expressions on both the sides.

function ::= functionname( parameters
parameters ::= expression { , expression }

Nesting of functions and expressions can be done freely as long as it does not go out of the comprehension of the system’s logic.

So, this was all in Qlik Sense Script Syntax. Hope you like our explanation.

7. Conclusion

This concludes our lesson on the Qlik Sense Script Syntax. We hope it helps you in working with the data load script in Qlik Sense better. However, if you have any queries related to Qlik Sense Script Syntax, drop your comments in the comment box below and we will be happy to help.

See also – 

Qlik Sense Career Opportunities

Reference for Qlik Sense