QlikView Tutorial & Interview Questions

QlikView Tutorials Overview

Welcome to QlikView Tutorials by QlikView Experts. In these tutorials, we will cover beginner to advanced QlikView Tutorials, Interview Questions, Issues and How To Do's.

QlikView Desktop

The QlikView Desktop is a Windows-based desktop tool that is used by business analysts and developers to create a data model and to lay out the graphical user interface (GUI or presentation layer) for QlikView apps. It is within this environment where a developer will use a SQL-like scripting environment (augmented by ‘wizards’) to create the linkages (connection strings) to the source data and to transform the data (e.g. rename fields, apply expressions) so that it can be analyzed and used within the UI, as well as re-used by other QlikView files. The QlikView Desktop is also the environment where all user interface design and user experience is developed in a drag-and-drop paradigm: everything from graphs and tables containing slices of data to multi-tab architectures to application of color scheme templates and company logos is done here. The file type that is created using the QlikView Desktop is known as a QVW (.qvw, or QlikView file). Upon reload, a QVW can be used to create a data-only QVD (QlikView data) file, which is binary and contains no UI.

QlikView Personal Edition

With QlikView Personal Edition, QlikTech offers a free version of QlikView for personal use. It is meant for individuals, students or small start-ups. QlikView Personal Edition is the full QlikView Desktop product and uses the same installation package. The only difference is that QlikView Personal Edition runs without a license key

If at any point you or your organization decides to acquire a full QlikView license, this Personal Edition installation will automatically become a full QlikView Desktop without requiring any additional software and without any changes to your QlikView documents. There are no limitations in terms of time or functionality in QlikView Personal Edition, however there are a few aspects to observe regarding the handling of QlikView documents. In QlikView Personal Edition, every document is saved with a user key that binds that file to the computer it is created on. This implies:

QlikView Personal Edition can only open files created using that particular copy of QlikView. This means, with QlikView Personal Edition you cannot use your QlikView documents on different computers, you cannot share your QlikView documents with another unregistered user, or open a QlikView document from another user (exempted are documents specially prepared for personal use by QlikTech). However, documents created in QlikView Personal Edition can be used with a licensed copy of QlikView desktop and they can also be published on a QlikView Server.

You cannot import an entire document layout (sheets/objects/security settings, etc. in XML format, without the data) into QlikView Personal Edition.

QlikView Server (QVS)

The QVS is a server-side product that contains the in-memory analytics engine and which handles all client/server communication between a QlikView client (i.e. desktop, IE plugin, AJAX or Mobile) and the server. It includes a management environment (QlikView Management Console) for providing administrator access to control all aspects of the server deployments (including security, clustering, distribution etc.) and also includes a web server to provide front-end access to the documents within. The web server’s user portal is known as Access Point. (It’s important to note that while the QVS contains its own web server, one can also utilize Microsoft IIS (Internet Information Server) for this purpose, too). The QVS handles client authorization against existing directory providers (e.g. Microsoft Active Directory, e Directory) and also performs read and write to ACLs(Access control lists)for QVW documents.

QlikView Publisher

The QlikView Publisher is a server-side product that performs two main functions: 1) It is used to load data directly from data sources defined via connection strings in the source QVW files. 2) It is also used as a distribution service to reduce data and applications from source QVW files based on various rules (such as user authorization or data access privileges) and to distribute these newly-created documents to the appropriate QlikView Servers or as static PDF reports via email.

QlikView Architecture

Data sources that can be readily accessed by QlikView include standard ODBC or OLEDB compliant databases, standard flat files such as Microsoft Excel, XML, etc. as well as from systems such as SAP NetWeaver, Salesforce.com, and Informatica.

QlikView and the IT Pro

QlikView’s approach to BI allows for a self-service model for business users on the front end while maintaining strict data security and governance on the back end. Because of this approach, IT professionals—from enterprise architects to data analysts — can remain focused on their core competencies: data security, data and application provisioning, data governance and system maintenance. They no longer have to spend time writing and rewriting reports for business users.

• In a typical QlikView deployment, IT professionals focus on:
• Managing data extracts and data and system security
• Creating and maintaining source QlikView files (QVWs and QVDs)
• Controlling data refresh and application distribution through QlikView Publisher
• Administering QlikView deployments via the QlikView Management Console (part of QVS)

QlikView and the Business Analyst / BI Developer

The role of a business analyst or BI developer in a typical QlikView deployment primarily involves the use of QlikView Desktop. QlikView developers use this Windows desktop application to extract data from source systems, create data models, and transform the data. It is where they describe all metadata, create data storage layers (QVD layers), and lay out the user interface. The BA or BI developer can also reuse existing extracted and pre-modeled data from the QVD layer by pulling “off-the-shelf” data from the QVD, when relevant for their application. The BA or BI developer also wants to ensure that their QlikView business applications are using the most recent data and that QlikView apps are being distributed to the correct business user community. For this reason, BAs and BI developers typically work closely with IT pros who use QlikView Publisher on the back end to ensure data refresh rates and QlikView Server on the front end to ensure the applications are correctly distributed and meet the business’ needs.

QlikView and the Business User

Business users interact with QlikView applications exclusively via the front end of a deployment, most typically using a browser on their desktop, laptop, or mobile device (such as an iPad). Users simply open their Access Point portal (or, in the case of integrated solutions, their organization’s own enterprise portal) and select the QlikView application they wish to use.

QlikView Server provides all client in-memory associative experience. Once the user’s security credentials are verified, they then open and can begin working with the application, exploring and interacting with the data and asking and answering their own stream of questions in a self can also collaborate with other users in the organization, sharing insights and exploring data together, in real time or asynchronously.

Dimension Model

Dimension Tables

Fact Tables

Dimension Table

It contains One primary key and relevant attributes to maintain business history

Example:Product Dimension

Fact Table

It contains set of foreign keys w.r.t to all the dimension tables associated facts information.

Facts are also called as Measures or Numerical information

Fact and Dimension tables will be interacted with the help of Primary key and Foreign Key relationship. Example:Sales Fact

It contains set of foreign keys w.r.t to all the dimension tables associated Facts are also called as Measures or Numerical information Fact and Dimension tables will be interacted with the help of Primary key and Foreign Key relationship

Operators in QlikView

There are two types of operators in QlikView, unary operators that take only one operand, or binary operators that take two operands. Most operators are binary.

Below are the different types of operators in QlikView

Numeric Operators (+,-,*,/)

String Operators (&,like) • &:

String concatenation. Eg: ‘Qlik’ & ‘View’ returns ‘QlikView’

Like: String comparison with wildcard characters. The operation returns a boolean true (-1) if the string before the operator is matched by the string after the operator. The second string may contain the wildcard characters * (any number of arbitrary characterEs) or ? (one arbitrary character).

Ex: 'abc' like 'a*' returns true (-1), 'abcd' like 'a?c*' returns true (-1) , 'abc' like 'a??bc' returns false (0)

Logical Operators (Not, And, Or, Xor)

Relational Operators (>, >=, <, <=,=, <> (Not Equivalent to), precedes(ASCII less than), follows(ASCII Greater than))

Bit Operators (bitnot, bitand, bitor, bitxor, >>, <<)

Naming of Tables

QlikView internal tables are named as they are stored in the QlikView internal database. Tables are named according to the following rules:

1. If a label immediately precedes a load or select statement the label will be used as table name. The label must be followed by a colon.
2. If no label is given, the filename or table name immediately following the keyword from in the load or select statement is used. A maximum of 32 characters is used. For filenames the extension is skipped.
3. Tables loaded inline will be named INLINE xx, where xx is a number. The first inline table will be given the name INLINE01.
4. Automatically generated tables will be named AUTOGENERATE xx, where x is a number. The first auto generated table will be given the name AUTOGENERATE01.
5. If a table name generated according to the rules above should be in conflict with a previous table name, the name will be extended with -x , where x is a number. The number is increased until no conflict remains. For example, three tables could be named Budget, Budget-1 and Budget-2.

Load Statement

The load statement loads 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.

Syntax: Load [Distinct] * from file; Load Fieldlist from file;

Load * inline [ data]; Load * resident resident tablename;

Load Fields Autogenerate size;

Load Fieldlist from file where criteria;

Load Fieldlist from file while criteria;

Load Fieldlist from file group by fieldlist;

Load Fieldlist from file order by fieldlist;

Distinct is a predicate used if only the first of duplicate records should be loaded.

Where is a clause used for stating whether record include in the selection or not. The selection is included if criterion is true.

While is a clause used for stating whether a record should be repeatedly read. The same record is read as long as criterion is true. In order to be useful, a while clause must typically include the IterNo( ) function.

Group by is a clause used for defining over which fields the data should be aggregated (grouped). The aggregation fields should be included in some way in the expressions loaded. No other fields than the aggregation fields may be used outside aggregation functions in the loaded expressions.

Order by is a clause used for sorting the records of a resident table before they are processed by the load statement. The resident table can be sorted by one or more fields in ascending or descending order. The sorting is made primarily by numeric value and secondarily by national ASCII value. This clause may only be used when the data source is a

Resident table The ordering fields specify which fields the resident table is sorted by. The field can be specified by its name or by its number in the resident table (the first field is taken as number 1).

Sort order is either asc for ascending or desc for descending. If no sort order is specified, asc is assumed. If no source of data is given by means of from, inline, resident, from field or autogenerate clause, data will be loaded from the result of the immediately succeeding select or load statement.

Variables in QlikView

Variables can be used in many ways in QlikView. They can have static values or they can be calculated. But when are they calculated? At script run-time or when the user clicks? And how should they be called? With or without dollar expansion? One basic way to assign a value to a variable is to use a Let statement in the script:

Let vToday = Num(Today()) ;

This will calculate the expression and assign it to the variable when the script is run. This is exactly what you want if you want to use a variable as a numeric parameter in your expressions.

But if you want the expression to be evaluated at a later stage, e.g. every time the user clicks, what should you do then? One way is to store the expression as a string in the variable, using either the Set or the Let statement or by defining it in the Document Properties -> Variables:

Set vSales = Sum(Sales) ;

Let vSales = 'Sum(Sales)' ;

In neither case, the expression will be calculated. The variable will contain the string ‘Sum(Sales)’, which subsequently can be used in an expression using a dollar expansion: $(vSales).

With a dollar expansion, QlikView will substitute the ‘$(vSales)’ with ‘Sum(Sales)’ before the expression with the dollar expansion is evaluated. Some of you will recognize this as an old style assembler macro expansion.

The subsequent calculation will be made based on the evaluation of the resulting expression. Note the two steps:

(1) Variable expansion

(2) Expression evaluation.

In the chart above, you can see the result of using a normal variable reference (the first expression) or using a dollar expansion (the second expression). In the second expression, the variable is expanded and the numbers are calculated correctly.

It is also possible to calculate the variable value, i.e. determine how it should be expanded, by using an initial equal sign in the variable definition.

Let vSales2 = '=Sum(Sales)';

In this case, the variable value is calculated after each click, whereupon the dollar expansion in the chart expression is made, and finally the expression is evaluated. This means that the evaluation of ‘Sum(Sales)’ is done before the variable expansion. Note the three steps: (1) Variable calculation; (2) Variable expansion; and (3) Expression evaluation.

The table below summarizes the three methods.

With the above, you can do almost magical things. You can for instance make conditional calculations that depend on e.g. selections, client platform or user.

Example

Create a field [Field to Aggregate] containing the names of two other numeric fields: 'Quantity' and 'Sales'

Create a variable vConditionalAggregationField = '=Only([Field to Aggregate])' 

Create a chart with an expression = Sum($(vConditionalAggregationField))

The calculation in a chart will now toggle between Sum(Quantity) and Sum(Sales) depending on your selection.

The use of variables is an extremely powerful tool that you can use to create flexible applications. Use it – but with caution. Too much magic behind the curtains can be confusing.

Different types of Loads in QlikView:

Loading from Excel, Text files

Resident Load

Inline Load

Auto generate Load

Mapping Load

Partial Reload

Buffer Load

Binary Load

Info Load

Bundle Info Load

Incremental Load

Cross Table Load

Generic Load

Interval Match Load

Preceding Load

Semantic Load