QlikView Script Statements | QlikView Keywords – Types and Syntax

1. QlikView Script Statements  – Objective

In our last tutorial, we studied QlikView Operators. Here, we will open a new chapter called QlikView Script Statements and keyword – the most crucial elements of Qlikview Scripting. Moreover, we will learn the different types of script statements and keywords in QlikView: script control, script regular, script prefixes, and script variables in QlikView.

The QlikView statements and keywords populate the script. Whenever you wish to perform a manipulation and control task in QlikView on the loaded data sets, you will have to do so by writing commands using these scripts and keywords.

So, let’s start QlikView Script Statements and keyword.

QlikView Script Statements & Keywords - Types and Syntax

QlikView Script Statements & Keywords – Types and Syntax

2. What are QlikView Script Statements and Keywords?

As we know, that any software using scripting as a means of controlling the functioning of the software, having the fundamental knowledge of QlikView script statements and keywords plays an essential role. The QlikView statements and keywords followed in computer language follow a certain standardization and formalism. Similarly, the QlikView script follows the Backus-Naur formalism. The QlikView script statements strictly follow the formulation rules, thus have a well-defined and structured script command language.

You will have to deal with these QlikView script statements and keywords a lot if you wish to work on QlikView and be a professional. So, we would solicit you to pay some extra attention and understand these statements fully so that you have a very strong fundamental knowledge of QlikView scripting and its elements.

We will discuss about the QlikView statements and keywords by broadly categorizing them in several categories i.e. control statements, regular statements, script prefixes and script variables. The prefixes and variables are keywords whereas, control and regular are two types of script statements in QlikView.

Do you know What is QlikView Directory Service Connector (DSC)?

Let us learn about all the important QlikView script statements and keywords.

QlikView Quiz

3. Types of QlikView Script Statements and Keyword

In this section, we are going to discuss about the uses and syntax of all the QlikView script statements and keywords.

a. QlikView Script Control Statements

The QlikView script control statements are used to manage and monitor the control flow of the script execution. A control statement can end with a semicolon or EOL (end of line).

QlikView Script Statements & Keywords - Types and Syntax

QlikView Script Statements & Keywords – QlikView Script Control Statements

  1. Call

Calls a subroutine along with defined parameters. Also, the subroutine must be in the script as ‘sub’ earlier, so that the user can call it through the call function.

Call name ([parameterlist])

  1. Do..loop

It executes one or several statements until or while a logical condition is met. It is a script iteration statement.

do [ ( while | until ) condition ] [statements]

[exit do [ ( when | unless ) condition ] [statements]

loop [ ( while | until ) condition ]

  1. Exit script

The Exit script, use for terminating the script execution and can insert anywhere in the script.

exit script[ (when | unless) condition ]

Follow this link to know about QlikView Ports

  1. For each ..next

The execution of one or several statements for each value in a comma-separated list.

The statements enclosed by for and next will execute for each value of the list individually.

For Each var in list [statements]

[exit for [ ( when | unless ) condition ] [statements] next [var]

  1. For..next

The QlikView script statements of for and next will execute for each value of the counter variable between (and including) specified low and high limits.

Forcounter = var1 to var2 [ stepvar3 ] [statements] [exit for [ ( when | unless ) condition ] [statements] Next [counter]

  1. If..then

It is a script selection construct. It controls script execution and makes it follow different paths depending on one or several logical conditions.

If condition then [statements]{elseif condition then [ statements ]}[ else[ statements ] ] end if

  1. Sub

The sub..end sub control statement defines a subroutine which can be called upon from a call statement.

Sub name [(paramlist)] statements end sub

  1. Switch

The script follows different paths, depending on the value of an expression during execution.

Switch expression {case valuelist [statements]} [default statements] end switch

b. QlikView Script Regular Statements

Qlikview regular statements are those statements which are commonly used for data manipulation. Every regular statement must end with a semicolon ‘;’

  1. Alias

Used to rename a data field temporarily.

Alias fieldname as aliasname {,fieldname as aliasname}

  1. Binary

It use to load data from another QlikView document files (QVD files), including section access data.

Binary file file ::= [ path ] filename

  1. Comment Field

It use for display the field comments from databases and spreadsheets. The last data value use as comment of there are repeating values of field name values.

comment *fieldlist using mapname

comment fieldname with comment

Have a look at – QlikView Distribution Service

  1. The Comment Table

Used to display metadata or table comments from databases or spreadsheets.

comment-table tablelist using mapname

comment-table tablename with comment

  1. Connect

Used to connect to the database through the OLE DB/ODBC interface.

ODBC CONNECT TO connect-string

OLEDB CONNECT TO connect-string

CUSTOM CONNECT TO connect-string

LIB CONNECT TO connection

  1. Direct Query

Used to access tables from ODBC/OLE DB connection using the Direct Discovery function.

direct-query [path]

  1. Directory

The Directory statement defines which directory to look in for data files in subsequent LOAD statements until a new Directory statement is made.

Directory [path]

  1. Disconnect

Used to terminate the ongoing ODBC/OLE DB/Custom connection.

Disconnect

  1. Drop Field

Used to drop/discontinue fields from table or tables that field exist in. Both drop field and drop fields forms of this statements are in use. The field will drop from all tables where it occurs if no specific table is mentioned in the statement. The dropped fields are also removed from the QlikView memory.

drop-field fieldname [, fieldname2 …][from tablename1 [, tablename2 …]]

drop fields fieldname [, fieldname2 …][from tablename1 [, tablename2 …]]

  1. Drop Table

Used to drop one or more internal QlikView tables both from the data model and from the internal memory.

drop-table tablename [, tablename2 …]

drop tables [s] tablename [, tablename2 …]

  1. Execute

It runs other programs while QlikView is loading data. Like, making necessary conversations.

Execute commandline

Let’s Explore QlikView SNMP (Simple Network Management Protocol)

  1. Force

It use fro interpret field names and field values and associates field values from data tables.

Force ( capitalization | case upper | case lower | case mixed )

  1. LOAD

It can use in several ways like, to load fields from a file, from data defined in the script, from a previously loaded table, from a web page, from the result of a subsequent SELECT statement or by generating data automatically.

LOAD [ distinct ] *fieldlist [( from file [format-spec] | from_field fieldassource [format-spec] inline data [ format-spec ] | resident table-label | autogenerate size )] [ where criterion | while criterion ] [ group_by groupbyfieldlist ] [order_by orderbyfieldlist]

  1. Let

This statement use for defining script variables. It is complementary to the Set statement as the set statement gives a calculated output and considers the value on RHS as an expression to calculate. If it is given that,

Let x= 3+2;

Then the result will be 6. Whereas in the case of Set the result would have been the string ‘3+2’.

Let variablename=expression

  1. Loosen Table

It is used to make some internal QlikView tables as loosely coupled tables.

Loosentable[s] tablename [ ,tablename2 …]

The [s] is to indicate that the statement can accept both as ‘table’ and ‘tables’.

  1. Map..using

Used for mapping a certain field value or expression to the values of a specific mapping table.

Map *fieldlist Using mapname

  1. NullAsNull

It is used to turn off the conversion of NULL values to string values. The terminates the action invoked by NullAsValue statement.

NullAsNull *fieldlist

  1. NullAsValue

Used to convert specific field values into NULL.

NullAsValue *fieldlist

  1. Qualify

Used to give field names the table name as a prefix. It is known as the qualification of the field names.

Qualify *fieldlist

  1. Rem

Used to insert remarks, or comments, into the script, or to temporarily deactivate script statements without removing them.

Rem string

  1. Rename Field

Used to rename one or more existing QlikView field(s).

rename-field[s] (using mapname | oldname to newname { , oldname to newname })

  1. Rename Table

Used to rename existing QlikView tables after they are loaded into QlikView’s memory.

rename-table[s](using mapname | oldname to newname{ , oldname to newname })

  1. Section

It is used to decide and define if, subsequent LOAD and SELECT statements should consider as data or as a definition of the section access rights.

Section (access | application)

Refer this – Top Companies using QlikView

  1. Select

Used to select fields from the data sources like ODBC or OLE DB. It is a standard SQL Select statement type.

Select [all | distinct | distinctrow | top n [percent]] *fieldlist

From tablelist

[Where criterion ]

[Group by fieldlist [having criterion ] ]

[Order by fieldlist [asc | desc] ]

[(Inner | Left | Right | Full)Join tablename on fieldref = fieldref ]

  1. Set

Used to define script variables as opposed to the use of Let.

Set variablename=string

  1. Sleep

It pauses script execution for a specified time.

Sleep n

Where n is any positive integer less than 3600000 representing time.

  1. SQL

It is used to send an arbitrary SQL command through an ODBC or OLE DB connection.

SQL sql_command

  1. SQL Columns

When a connection with ODBC or OLE DB data sources is made, this statement returns a set of fields describing the columns the data source.

SQLColumns

  1. SQL Tables

Returns a set of fields describing the tables of an ODBC or OLE DB data source after connecting to the source.

SQLTables

  1. SQL Types

Returns a set of fields describing the types of an ODBC or OLE DB data source which is connected to QlikView.

SQLTypes

  1. Star

In order to represent entire value set from a database, a string is created and selected using the star statement.

Star is [ string ]

  1. Store

Used to create a QVD or a CSV file.

Store [*fieldlist from] table into filename[format-spec ];

  1. Tag

Used to assign tags to one or more fields in a table.

Tag fields fieldlist using mapname

Tag field fieldname with tagname

  1. Trace

With the help of this statement a string is written to script execution progress window and to the script log file, when they are in use. It is of significance in dubbing processes.

Trace string

  1. Unmap

It is used for disabling the filed value mapping.

Unmap *fieldlist

  1. Unqualify

It used to turn off the qualifications applied on the field names using the Qualify statement.

Unqualify *fieldlist

  1. Untag

Used to remove tags from one or more fields.

Untag fields fieldlist using mapname

Untag field fieldname with tagname

Follow this link to know about QlikView Sheet and Object

c.  QlikView Script Prefixes

QlikView script prefixes (QlikView keywords) are only applicable before regular statements; control statements never use prefixes. They might however use some suffixes.

  1. Add

Can add to any LOAD, SELECT or map…using statement in the script.

Add[only](loadstatement|selectstatement|mapstatement)

  1. Buffer

This prefix is used to create and maintain QVD files. It can also use on load and select statements.

Buffer[(option[,option])](loadstatement|selectstatement )

  1. Bundle

The Bundle prefix is used to include external files, such as image or sound files, or objects connected to a field value, to store in the qvw file.

Bundle [Info] ( loadstatement | selectstatement)

  1. Concatenate

This prefix is used to join or concatenate two tables even if the tables have different sets of fields.

concatenate[(tablename)](loadstatement|selectstatement )

  1. Crosstable

This prefix is used to convert a cross table into a straight table. That means, a horizontal table with many columns is turned into a vertical table, with the column headings being placed into a single attribute column.

crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )

  1. First

This prefix is used for loading a fixed maximum number of records from a data source table. It can also place in front of LOAD and SELECT statement.

First n( loadstatement | selectstatement )

  1. Generic

This prefix is used to unpack a vertical table and create a table with one field per attribute value.

Generic ( loadstatement | selectstatement )

  1. Hierarchy

This prefix, use to transform a parent-child hierarchy table to a QlikView data model suiting. It can place in front of a LOAD or a SELECT statement

Hierarchy (NodeID, ParentID, NodeName, [ParentName], [PathSource],[PathName],[PathDelimiter],[Depth])(loadstatement | selectstatement)

Have a look at – Working of QlikView

  1. HierarchyBelongsTo

It serves the same purpose as the above Hierarchy prefix, but this prefix also specifies the ancestor table which the transformed parent-child hierarchy table beolongs to.

HierarchyBelongsTo (NodeID, ParentID, NodeName, AncestorID, AncestorName, [DepthDiff])(loadstatement | selectstatement)

  1. Image_Size

This prefix, use to resize images from a database management system to fit in the fields. It is most commonly used with Info or Bundle prefix.

Info [Image_Size(width,height )] ( loadstatement | selectstatement )

  1. Info

Infor, use to link external information, such as a text file, a picture or a video to a field value.

Info( loadstatement | selectstatement )

  1. Inner

This prefix is generally use with Keep and Join prefixes. We have learned about the Keep and Join prefixes earlier in the tutorial. When an Inner Join is used, only the combination of similar fields is displayed in the joined table. Whereas, when Inner Keep is used, only the common intersecting fields are shown in the resultant table.

Inner ( Join | Keep) [ (tablename) ](loadstatement |selectstatement )

  1. IntervalMatch

QlikView IntervalMatch, use to create a table where the numerical values from one table matched with the duration or intervals in another table.

IntervalMatch(matchfield)(loadstatement|selectstatement )

IntervalMatch (matchfield,keyfield1 [ , keyfield2, … keyfield5 ] ) (loadstatement | selectstatement )

  1. Join

It use to join two tables during one table is loading and second table is pre-existing or is also in the state of loading.

[Inner | Outer | Left | Right ] Join [ (tablename ) ]( loadstatement | selectstatement )

  1. Keep

The Keep prefix reduces and joins the common fields in two tables and displays one table as a result. Unlike in the Join, Keep does not merge thw two tables in the QlikView Memory and save it as one, but the two tables merged using Keep are stored separately.

(Inner | Left | Right) Keep [(tablename)]( loadstatement | selectstatement)

Let’s Explore QlikView Keep Functions

  1. Left

The Left prefix use with Join and Keep keywords and it commands the system to fetch the data values from the left table and display a final table having the left table fields and the fields in common with the left and right tables.

Left (Join | Keep) [(tablename)](loadstatement |selectstatement )

  1. Mapping

This prefix use to create a mapping table that can use for purposes like replacing field values and field names during script execution.

Mapping ( loadstatement | selectstatement )

  1. NoConcatenate

If two tables have identical fields then they are automatically concatenated by during execution. To keep these tables separate and avoid automatic concatenation, NoConcatenate prefix is used.

NoConcatenate( loadstatement | selectstatement )

  1. Outer

The Outer prefix use with the keyword Join and is used to join the tables having identical fields and make a complete table without repeating similar fields in the final table.

Outer Join [(tablename)](loadstatement |selectstatement )

  1. Replace

The Replace prefix replaces an already existing table with a new table while loading. The old table drops and replace by a new one.

Replace[only](loadstatement|selectstatement|map…usingstatement)

  1. Right

Just like the Left prefix, Right prefix use to only fetch the data/field values from the right table and the resultant table will have fields from right table and the fields in common between the right and the left table.

Right (Join | Keep) [(tablename)](loadstatement |selectstatement )

  1. Sample

The sample prefix, use to load a sample of records from the data source arbitrarily. It is used with LOAD and SELECT statements.

Sample p ( loadstatement | selectstatement )

  1. Semantic

The Semantic prefix use by a record to refer or point towards another relevant record like, self-references within a table.

Semantic (loadstatement | selectstatement)

  1. Unless

Unless, is use to decide whether a statement should evaluate during execution or not. It is like a short alternative to the full if..end if statement. It use as a conditional statement.

(Unless condition statement | exitstatement Unless condition)

  1. When

It is similar to the Unless statement as it is also a conditional statement and decides whether or not a statement or exit clause will evaluate during script execution.

(When condition statement | exitstatement when condition)

Also, Refer QlikView Circular Reference & Loosely Coupled Tables

d. QlikView Script Variable

The QlikView script variables (QlikView keywords) are containers that contains data values. The values can be string or calculable values. If a defined value has ‘=’ sign in front of it, then the value will evaluate. There are several types of script variables in QlikView are:

i. Error Variables

ii. Number Interpretation Variables

iii. System Variables

iv. Value Handling Variables

v. Direct Discovery Variables

So, this was all about QlikView script statements and keywords. Hope you like our explanation,

4. Conclusion

Hence, this was a complete categoric account of QlikView script statements and keywords. We took a comprehensive view of two types of QlikView script statements; control and regular statements and two types of QlikView keywords; prefix and variables. Still, confusion? Free to share with us, surely we will get back to you!

Related Topic –  QlikView Distribution Service

Reference 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.