Qlikview Scripting Features and Functions

Qlikview Scripting Features and Functions

Scripting is a very powerful feature in QlikView, which enables control of the data load options and data transformations. It enables the use of many inbuilt functions available in QlikView and creates subroutines to be used across multiple scripts within a QlikView document. Scripting is done using the Script Editor.

This post discusses about basic script structure, features, and the functional areas of a QlikView script. A QlikView script contains several sections that are mandatory (number interpretation variables, script expressions, and load scripts) and some that are optional (master calendar and variables). One common feature you will often notice in scripts is the renaming of field functions (Alias, AS, Rename Field/Rename Table, and QUALIFY). The renaming of fields is a typical feature of scripts.

Number interpretation variables:

When you load data containing numbers, currency, or dates, it will be interpreted differently depending on whether the data type is defined or not.

One of the first things you will notice while opening the Script Editor, for a new QlikView document, is the number of interpretation variables. Number interpretation variables are system defined, that is, they are automatically generated according to the current regional settings of the operating system when a new document is created. In QlikView Desktop, this is according to the settings of the computer operating system, and in QlikView, it is according to the operating system of the server where QlikView is installed.  Most of these variables (which control how numbers, currency, time, and dates are displayed and handled) populate by default when creating a new QlikView document. When a QlikView script is run, these variables override any similar settings from the computer operating system.

The variables are included at the top of the script of the new QlikView document and substitute operating system defaults for certain number of formatting settings at the time of the script execution. They can be deleted, edited or duplicated freely.

The following is an example of number of interpretation variables in a script. Each Set statement defines a variable, such as a comma (,) for the variable ThousandSep, which separates numeric thousands.

Number interpretation variables can be edited (click on the gray hammer icon to the left of the Set statement corresponding to each variable). You can add more number interpretation variables by using the Insert | Set Statement menu command in the Script Editor.

Connect, Load, and other statement ordering:

QlikView scripts consist of a number of statements and expressions. A statement can be either a regular script statement or a script control statement.

While Creating the Script, we get connected to a database and established the Connect statement in the script. You will notice that the first major feature of most scripts, after the initial number interpretation and any variable settings, is the Connect statement (if you are connecting to a database). If you are not connecting to a database, a Connect statement is not present. In the case of not connecting to a database, you are probably using table files or other files for the data source, so only the Load statement is present (with no Connect statement). The general order and definition of these statements are:

• Connect: This statement establishes a link to a database via an ODBC or OLE DB driver. The Connect statements are only required when a database source is present and needed for data extraction. The connection will cease upon a DISCONNECT statement, upon a new Connect statement, or at the end of the script execution.

• Load: This statement inserts fields from an external file (such as Excel, text, HTML, and QVD), from data defined in the script, an inline table, a previously loaded table, previously loaded fields, as the result of a following Select statement, or by generating data automatically.

• SQL Select: This statement performs a full SQL query of the fields and tables to load from the current database connection. The Select statement is not needed if loading from a file, inline, or script-generated table.

Frequently Asked QlikView Interview Questions & Answers

The following screenshot shows an illustration of a typical script Load statement, including the Connect and Select statements. In this example, the Connect statement is in the format Connect32, which forces a 32-bit connection with a 64-bit system. Note, there is also a Connect 64 format, which forces a 64-bit connection. Also, note the prefix to the Connect function: possible prefix values are ODBC, OLEDB (it is by default, if no prefix is used), and Custom (for custom database drivers or connectors). At the end of the Connect/Load/Select block of code, there is a Store statement, which stores the extracted information into a QVD file.

Script Segmentation via tabs:

Creating the script, various strategies were described for dividing the script among tabs in the Script Editor. There are many preferences while choosing the script architecture, but the one which is most accepted, in the QlikView community, is dividing the script tabs by data source and also creating additional tabs for the master calendar, variables, mapping tables and cleanup procedures. Remember that the scripts are executed from top to bottom and from left to right, so that the tab scripts are executed in the correct order.

Related Page: Process Of Incremental Load In QVD QlikView

One related note about segmentation of the script (and associated QVWs) is that a common architecture must have a three-tiered QlikView environment:

• Tier 1 application files are the data connection layer. The QVW connects to the database and runs the SQL queries of database table to generate QVDs.

• Tier 2 is the transformation layer where transformations are performed by tier 2 QVWs with data from tier 1 QVDs. This information is stored in tier 2 data model QVDs.

• Tier 3 is the GUI/presentation layer, which uses tier 2 QVDs for the data model.

Adding, removing and other scripting tab manipulation can be achieved via the Tab menu in the Script Editor.