QlikView Tutorial

Qlikview is a very popular BI (Business Intelligence) tool and a leading Business discovery platform. This Qlikview tutorial gives complete information regarding Qlikview, right from its features, architecture, to its installation, work procedure, and its various other concepts. 

Thriving in a world of information, we are pushed to handle the Big Data problems in our everyday work, at the office. Each enterprise faces its own problems, associated with data curation and analysis. The dull figures and numbers often saturate our brains with unsolved information. We need brighter and effective ways of data visualization and data handling.

Qlikview is one such tool that is programmed to perform data analysis, adopting better and more effective ways of data visualization. It is one of the leading BI platforms in the market. It is important to get acquainted with Qlikview and understand the effective differences between Qlikview and significant other peers so that we can adopt the right one for our requirements.

Qlikview Tutorial - Table of Content

What is Qlikview Features of Qlikview Comparison Between Qlikview and Tableau Qlikview Big Data  What is Big Data? Handling Big Data along with Qlikview Qlikview Components Qlikview Architecture Qlikview Desktop Installation Qlikview Navigation Qlikview Report Interface Qlikview Data Model Qlikview Data Analysis Qlikview Data Transformation

What is Qlikview

Qlikview is one of the fastest developing Business Intelligence and data visualization tools which aims to provide all enterprises (big and small) with data discovery and analysis.

Approximately 30,000 organizations across the world are happy customers of Qlikview. It is an easy application to load or deploy, with a very intuitive user interface. It helps you with better data visualization, by representing the relationship between data, using different colors. This enhances understanding and improves the degree of data analysis.

It is a patented technology for in-memory data processing, which aims to improve the rate of data processing. One of the highlights of Qlikview is that it compresses the data to 10% of its original size, enabling it to handle big data efficiently.

Do you want to get certified and build your career in Qlikview? Then enroll in "Qlikview Online Training" this course will help you to achieve excellence in this domain.

Why should you learn Qlikview?

Information is the king in the present world. For example, the shares and investors breathe on data. The weather forecast has its base in data. Facebook and Instagram live on data. Forget it. Everything runs on data. It is the data that is a boon and bane at the same time. The world is overflowing with data in all spheres. We need to efficiently manage it and utilize its metrics, to thrive well today.

Understanding and analyzing the whooping information day in, day out, through facts and numbers becomes a weak approach. We can hardly perceive and remember everything in numbers. We need charts, diagrams, graphs, and colors to dictate us through the data analysis. We will not only analyze data better through the visual medium, but we will also get the details that otherwise would go missing.

Qlikview is built to cater to the needs of handling big data with better visuals and adopting a better data analysis approach. It has many critical features that ensure complete user interaction and satisfaction while dealing with data.

Explore - QlikView Interview Questions

Why is Qlikview so widely used?

There are a couple of powerful reasons that cause a wide range of acceptance of Qlikview across the companies. Some of them being: in-memory storage and effective data visualization. 

There is no need for any data staging or to store it in any intermediate forms like cubes, hence enabling Qlikview to pull data extremely fast. 

It also efficiently offers associative search, to enable data searching, both directly and indirectly. The combination of these advantages is seldom seen in other substantial competitors in the market.

All these attributes, together, enable meticulous data analysis along with faster data processing. 

Features of Qlikview

Some of the important features of Qlikview are given below:

1. Detects the relationship between data: Qlikview automatically recognizes the relationship between the data in the database. You don't have to pre-configure the relationship.
2. In-memory data: The data associated with the data aggregation and calculations are stored in memory. This reduces the overhead memory requirement in case of the big data and also enhances the performance.
3. Aggregations are faster: Since the data is stored in memory, the aggregations are done on a fly.
4. Compression: Qlikview ensures that only useful bits of data are considered, removing all the redundant bits. This compresses the data to 10% of its original size.
5. Data visualization using colors: As an important feature, the data visualization is done using colors by the Qlikview application The related data and unrelated data are differently colored for better transparency and understanding.
6. Direct and Indirect Searches: Qlikview supports both direct and indirect searches of data. A user can search for particular data by giving in, any related information and get the required result. A direct search is also allowed.
7. Associative Technology: Qlikview is regarded as the world's first associative architecture. The in-memory storage is associative in nature, where the individual tables are stored. There is a well-defined association (relation) between every point to every other point in the schema.
8. Scalable solution: Initially, the enterprises can buy the software on a smaller scale and then can scale it up, as and when the need arises.

QlikView Features for Business Users:

1. They can reveal concealed patterns and settle on disclosures that drive inventive choices for making better business operations by analyzing the data-driven results on each operation's performance.
2. They can make inquiries and seek after bits of knowledge all alone or cooperatively - on the fly, out, and about a department or a business operation.
3. The pursuit over every data by the authorized business users can acquaint business outcomes and look into new associations and connections made over the valuable information
4. The self-beneficiary Online cloud BI platform without the need to backpedal by IT for creating the reports and dashboards.
5. They can stay no worry-free about information security, quality, and administration authorities for every data transaction made on private information.

QlikView Features for IT Professionals

1. They can merge information from different data sources to convey an entire information picture to business clients as per the business needs and objectives.
2. They can peacefully concentrate on information security, storage and application provisioning, information administration, and framework support.
3. They can take care of composing and modifying the reports made up of tweaking questions and building solid shapes as per the needs of officials on the fly.

Explore - Script Editor Features and Commands in QlikView

QlikView Features for Developers and Designers

1. It makes life easier for Developers as they easily develop and deploy the modules that make business investigations
2. The BI designers make bleeding-edge applications that clients can upgrade and stretch out as per the agile environments.
3. Effectively make and oversee information definitions, changes, and business rationale, and reuse them crosswise over QlikView applications
4. By utilizing QlikView Workbench (a Microsoft Visual Studio® module)they can make expansions to their BI Tool.
5. They can interface any Big data business applications like Salesforce.com, SAP NetWeaver® Informatica, Teradata, Hadoop, and Google BigQuery

Comparison Between Qlikview and Tableau

Feature	Tableau	Qlikview
Deployment Process	It does not own a data warehouse.  It cannot create layers while connecting to the data set. The process of deployment is easier than Qlikview, as it deals with structured data.	It has its own data warehouse where multilevel layers can be used for deployment.  It starts to produce reports within minutes of installation. It does not use cubes. Hence it loads tables and charts in memory to enable interactive queries and the creation of reports.
Ease of Use	The Interface is simple to use. It has a drag and drops feature It doesn’t allow you to search for content across all the data. You can create views easily with various objects due to an advanced GUI.	The Interface is simple to use. You can search for any data trend by typing in data in the search box. You will be presented with the required connections and relationships. However, it is hard to create views, because of menu-driven design.
Ease of Learning	A simple drag and drop feature enables you to learn things quickly.	You can learn to work with Qlikview easily, being guided by community support.
Cost	The desktop “public version” is free of cost, with the limitation to download all your data. The private version costs $999 or $1,999 depending on the data access. Tableau server costs $1000/server user with a minimum of 10 users and added maintenance.	Its personal version is free. It costs $1350 for each named user and an additional cost of $15000 for every concurrent user. Server license costs you $35000 per server. Additional PDF distribution service costs $21000 for each server. SAP NetWeaver costs an added amount of $22500.
Connectivity with other tools	It can integrate with data sources like CSV, SQL databases, Salesforce, Teradata, Cloudera Hadoop, Firebird, Google Analytics, Google BigQuery, HP Vertica, MS SQL Server, MySQL, OData, Oracle, Pivotal Greenplum, Hortonworks Hadoop, PostgreSQL, Salesforce, and Windows Azure Marketplace. It can be integrated with R. It can connect to Big Data sources.	It has very good capability of integration with Amazon Vectorwise, EC2, and Redshift, Cloudera Hadoop and Impala, CSV, DataStax, Epicor Scala, EMC Green Plum, Hortonworks Hadoop, HP Vertica, IBM DB2, IBM Netezza, Infor Lawson, Informatica Powercenter, MicroStrategy, MS SQL Server, My SQL, ODBC, Par Accel, Sage 500, Salesforce, SAP, SAP Hana, Teradata, and many more. You can integrate R using API. It can connect with Big Data.
Mobility	It is available on all the devices	It is available on both web-based and mobile-based clients.
Maintainability	Easy to maintain	Easy to maintain
Security	It provides much-secured mechanisms to access each of the tableau features.  It helps organizations, to promote the protected data sources for all users, in order to authenticate the fragile data content.	It provides security for the documents, scripts, and sections through user authentication. Document access through the Qlikview desktop is managed by Windows NTFS File Security. The user access to the web-based Qlikview Enterprise Management Console is limited to only Windows users who are a part of the particular Windows group.

 

Explore - Table Viewer in QlikView

Qlikview Big Data 

The term Big Data is applied to the set of data where the size of the data is beyond the capability of the generally used software tools to manage, capture, and procedure the series of actions within the given time elapse.

According to the report provided by IDC, the content in the digital world will reach the heights of billion terabytes plus of data with which it becomes highly tough to be sustainable for managing and structuring the data for the concepts under the Big Data categorization as Volume, Velocity, and Variety. To avoid the big data capabilities, it ties up with the most flexible business intelligence platform Qlikview which turns the data into knowledge.

The most adorable features of Qlikview Big data is its flexibility to adapt to the data, technology associative in nature, it offers speed of thought, analytics within the self-service with the big data which was a critical and effective success for getting numerous organizations almost close to 24000 plus organizations reach and use to analyze their data with the help of Qlikview big data to enhance their business insights.

Big data detect new trends in the market and discovers fraud data or duplications in the data which became an added advantage to the big data. The other specific and most fascinating feature of Big data is its expansion towards its capability of data storage with which most of the business users such as managers of product, business, marketing, sales, advertising, game designing business, and customer service companies utilize the advanced features of Big data and got furnished by using it for making decisions which are mandatory on daily basis.

These Big data helped most of the businesses for a longer time in every aspect of their business needs and gained a deeper understanding of the structure of the big data and enjoyed comfort using it. But within no time, most organizations came up with the concept of extra and prominent capability self-service where the users can remix and re-engineer the big data to achieve new insights with plenty of dimensions available.

Where in real-time, the IT industry’s got the requirement for tremendous solutions with analytics that can handle the billions amount of data within a self-service manner. Then Qlikview came into the picture where it can merge with big data and can harvest the value of the big data. Henceforth, most IT and non-IT business users got attracted to the Qlikview analytics application which enables the business users to seamlessly maintain a million rows of data.

Explore - QlikView vs Qlik Sense

What is Big Data?

In short, Big Data is referred to be as the size of data that matters more in every action performed in the data we have. Big Data is a term of data groups that are too large and complex. Big Data also refers to the use of predictive analysis, user behavior analysis, and definite other advanced data analytics methods which can be extracted from data.   Big data has a challenge of its three dimensions: Volume, Variety, and Velocity.

As per 2015, Big data has a volume of 8 billion terabytes of data in the world; Variety types are Structured, Unstructured, and Semi-structured; Velocity means updates of machine status and streaming data, etc.,. it became a challenging task for businesses to achieve agile property when finding new insights for most emerging types of data and content.

Henceforth every business is running hard to harvest finding new solutions on daily basis support to ease their process of data storage, retrieval, and other performances on their data.

Handling Big Data along with Qlikview

Qlikview along with big data is an admirable combination for finding more insights for the business users.:

• Consolidate data that is relevant from several sources, including Big Data archives
• Chooses the method which is most relevant for you and your IT infrastructure
• Make optimal use of existing investments in Big Data infrastructure or data warehouses
• Gain access to Big Data’s complex data modeling or programming
• Investigate associations between Big Data and traditional data
• Visualize Big Data with existing, advanced graphics
• Gain access to and analyze Big Data on mobile devices

Analysis of memory:

Qlikview provides a great solution that is fast and powerful for the analysis of memory.

App on Demand – API:

This API technique enables Qlik apps to create temporary applications or session applications or permanent applications based on the inputs provided.

Explore - QlikView  Management API

Direct Query:

In few cases, we may require a large amount of data with high speed where Qlikview helps with its Query mode feature. By using we can get the data accordingly as per our need with fast speed and accurate results.

Qlikview Components

Qlikview is made of a number of components, which together function as a powerful BI tool. Below are the components, that makeup Qlikview.

1. Qlikview Desktop: Qlikview Desktop is a Windows-based application, which acts as a single source for the extraction, transformation, analysis, design, and construction of the dashboard and the reports. Developers can make SQL-like scripts (also with the help of wizards) and run them on the Qlikview Desktop interface. 
2. Qlikview Publisher: Qlikview publisher is the optional module of the Qlikview server. It works as the planning, administration, and management tool, which functions as a common point for the Qlikview analytical applications and reports.
3. Qlikview Server: Qlikview server mainly performs the function of fetching the requested document from across the internet and intranet.

Qlikview Architecture

The diagram below gives an overview of the architecture of the Qlikview. 

There are two main segments in the Qlikview architecture. One is front-end and the other is back-end. The front-end is used to visualize the processed data, while the back-end provides the security and publication mechanism for the user's documents.

Explore - QlikView Architecture

Front end

This is the front face for the Qlikview application, which is the interactive interface for the end-users. It is the singular, browser-based access point to view the user documents and other available data (which they are typically authorized to see). It is mainly made of the Qlikview Server, which is used to access the already created BI reports using the internet or intranet through URL. The user authentication and other security concerns are taken care of by the Qlikview Server.

The front-end is dependent on the resources offered by the infrastructure of the system.

The documents and reports created by the business users on the front-end are stored in the .qvw format, which on Windows is recognized as a standalone document. The user documents that you can see here are the ones created by the Qlikview Publisher. 

The front end is the main interface, where the client-server communication happens.

Back end

The back-end is mainly made of the Qlikview Publisher and Qlikview Desktop. The Qlikview source documents, which are created by the Qlikview developer, are stored here. These source files have the script expressions required to pull the data from different data sources, like data warehouses, Microsoft Excel files, SAP, and Salesforce.com. The main function of loading and distribution is carried out by the Distribution Service.

The back-end relies on the resources from the infrastructure for clustering. Other services like SMTP and directory catalogs are also accessed by the back-end for various purposes.

The Qlikview Desktop is the wizard-driven environment, which is responsible to load and distribute data from its source. It is here that the drag-and-drop feature is implemented to create GUI layouts of the reports, which will be viewed in the front end.

Qlikview publisher is used for the distribution service. It is used to distribute the created .qvw files between the users and the servers. It performs the direct loading of the data from the data sources using the connection strings that are written in the .qvw files.

Using Visual Cues

Visual cues are a handy (and perhaps simpler) way to create visual color alerts in tables. To create a visual cue, follow these steps:

Holding Ctrl pressed, click-and-drag the chart you just made to another area of the sheet. This will duplicate the object (and is a very useful trick). In the dimensions tab, delete the expression we used to set the background color.

• In the upper field of the visual cues tab, enter Avg (total cases). Set the text to black and the background to light blue.
• In the lower field, set the text to black and the background as light red. Check the bold box to change the font to bold.
• In the presentation tab, set the totals to appear on the last row, and in the expression tab, set the totals to appear as expression total on the last row. Select the use of label checkbox and enter total cases. Click. It appears very much like the previous chart, except for the font and totals row.

Using the Alternate States

By default users, selections in QlikView are stored in ‘$’. Alternate states make it possible to create additional selection states besides $.

Alternate states can help business users compare two different dimensions (whether they are related or unrelated). It performs a comparative analysis between sets of multiple data items. The developer creates multiple states within a Qlikview document and applies them to specific objects. Alternate state functionality is not accessible in the load script.

An analysis like this can be quite useful, particularly when combined with set analysis (described in the next section), because state settings can be used in an expression in conjunction with a set modifier. Alternate states refer to setting a state of a sheet or object, and sheets and objects with that assigned state will respond to each other. Objects of different assigned states will be disconnected from one another.

Follow these steps to set up alternate states:

1. Enable the alternate states to feature for the application in the document properties dialog, via the alternate states button in the general tab, click on the add button to add the name of an alternate state, such as alternate state1, state1, or any description, as shown in the following diagram:
2. Now that alternate states have been added to the document properties, the alternate states feature is activated. This adds a new alternate state drop-down list to the object properties tab, as shown in the following diagram:
3. You can use alternate states to assign an alternate state to a chart and list box any objects assigned to the same alternate state will be linked and associated. This is usually used with a list box that controls the function of a chart-in in this case, both the list box and the chart will have the same assigned alternate state name.
4. You may wish to have two identical list boxes and two identical charts, with each list box and chart pairing having its own alternate state assignment. This allows the business users the ability to dynamically compare two separate dimensions (two different products, salespersons, regions) against some measure.

Key points to remember about alternate states:

• alternate state sheets and objects are disconnected from other selections that are made elsewhere unless those selections were made on an object with the same alternate state assignment.
• this disconnected from the entire document does limit alternate state functionality. It is more powerful when combined with set analysis.
• the document, and any unassigned sheets and objects, always have an alternate state assignment of default.
• objects can also inherit an alternate state of a parent object. For a sheet object, this can be the alternate state setting of a container, or from a sheet.

Qlikview Desktop Installation

The steps for Desktop installation with necessary directions are explained below.

Download Qlikview

You need to first download the personal edition of the Qlikview. You can download it from the Official Qlikview Website. You have to fill in the details and register to be able to download it. 

Once the file is downloaded, you have to agree to the License Agreement and select the target folder for installation to proceed.

The images for the installation process are given below.

Start the installation wizard

Double click QlikViewDesktop_x64Setup.exe to open the language selection window. Clicking in English will present you with the following screen. Click Next.

Now accept the license agreement, by choosing the "I accept the terms in the license agreement" option. Then click on Next.

Now provide your details, such as your name and organization details, and click Next.

Choose the destination folder of your choice or retain the default one.

Now, choose the setup type as complete and click on Next.

This is the screen where you begin the process of installation. If you like to change any configuration, you can still choose to go back and change, else proceed with "Install"

Once, the installation is completed, the finish screen appears. Now, click on Finish.

You can now check on the installation, by going to the start menu and choosing the Qlikview icon. The screen shown below appears.

This ensures a successful Qlikview Desktop Application.

Leave an Inquiry to learn QlikView Online Training in Bangalore

Qlikview Navigation

This chapter deals with the basic operations, a user can perform in the Qlikview User Interface. 

We will get acquainted with all the icons available on the screen and their respective functions. 

Once the Qlikview Desktop Edition is installed, launch the application. The first screen presented to you is the getting started screen.

Getting Started screen

This is the first screen of the application, upon fresh installation.  However, you can check the "Show start page when launching Qlikview" option, given at the bottom of the page, to go through this page, every time you launch the application.

The screen has two divisions. The left division contains the examples and getting started document. Ensure to scroll through the examples, to understand how QlikView works. The right division of the screen stores the "Recents" and "Favorites". The "Recents" will store the recently opened documents and the "Favorites" will consist of the documents that you want to visit frequently.

Menu Commands

Upon closing the ‘Getting Started’ window, you will be presented with the menu window with all the available menu commands. They represent a complete set of features of Qlikview. Below are the details of each section of Menu commands.

File Menu

This menu is used to perform two main functions:

1. Create a new file 
2. The existing files are from both the Qlikview server and the local system.

The other important features of this menu are:

• Mail as an attachment: You can email the currently opened mail as an attachment.
• Reduce Data: This is used to view only the database structure and report layout, without any data.
• Table Viewer: This option helps in viewing the structure of the tables and fields with their respective relationship in a graphical format.

Edit Menu

Edit menu has tools required to perform editing functions like copy, paste, cut, and format painter. Some of the important features of this Menu are:

• Active all: This activates all the sheet objects present in the active or opened sheet.
• Remove: It is used to remove a sheet from the active window.
• Advanced Search: It helps to perform a search with advanced search expressions using a multi-box.

View Menu

This menu basically contains various view options. Some of the important functions of the menu are:

1. To view standard toolbars
2. To view all the active sheets in a cascade menu
3. Turn on/off the WebView mode to switch between WebView mode and local view mode.
4. The current selections option displays the field name and file values of the selected sheet objects.
5. Design grid helps to toggle the sheet object placeholders for active objects and snap-to-grid to moving and resizing the objects in the layout.
6. Zoom in, Zoom out features.

Selection Menu 

This is used to select and deselect the values in the sheet objects. It also helps in going back and forward, the logical statements of the sheets that are currently active. Other important features are:

1. Lock feature allows you to lock all the values in the current selection
2. Unlock feature allows you to unlock all the locked values in the current selection.

Layout Menu

The layout menu is used to add the sheets, select different sheet objects and rearrange them. Some of the important features of this menu are:

1. Promote sheet option moves the current tab/sheet one step forward.
2. Demote Sheet option moves the current sheet/tab one step backward.
3. Delete Sheet option deletes the active sheet and all the objects associated with it.

Settings Menu

This menu allows the user to set user preferences, document properties, and Sheet properties. Some of the other associated functions are:

1. Variable Overview displays all the non-hidden variables along with their value in a single list.
2. Expression Overview displays all the expressions of the objects, documents, and sheets in a single list.

Bookmark Menu

This menu helps to bookmark various documents for easier retrieval.

Reports Menu

This menu allows you to create reports, edit existing reports, add or remove layouts to and from the reports and delete reports.

Tools Menu

This is one of the important menu options in Qlikview. It lets you create charts and use the Qlikview management console. Some of the important features of this menu are:

• Quick Chart Wizard helps in creating new charts without using many settings or options. 
• Time Chart Wizard helps in creating time series charts
• Statistics Chart Wizard helps in performing common statistical tests on data.

Object menu

This allows you to create new sheet objects and also modify the existing ones. The properties option displays various properties associated with a particular sheet object. Other important features of this menu are:

1. Copy Sheet helps to create a copy of the sheets along with all its objects.
2. Cope Image to Clipboard helps to copy a bitmap image of the sheet onto the clipboard.
3. The remove option removes the sheet along with its objects.

The Window menu helps in organizing different windows of the Qlikview application and the Help menu provides the Help documentation of Qlikview.

Data Loading

This topic covers details of data loading in Qlikview. The data can be loaded into Qlikview using multiple ways. It accepts data from various file formats like Excel, XML, etc. Following are the detailed explanations for data loading in Qlikview:

1) Excel File

Qlikview can easily accept an excel file. You have to just open the Qlikview window, drag and drop the excel file into it. This will automatically create the sheet in the interface with excel data.

Select the Excel file: Open the main window of Qlikview and browse for the target excel sheet.

Select a Data Source: Upon dropping the excel file into the Qlikview interface, a file wizard appears. The file format "xlsx" is chosen by default in the wizard, as Qlikview auto-detects the file type. Under "Labels", choose Embedded Labels and click "Next step" to continue.

Load Script: The load script screen has the data loading script written. This is a SQL-like script and is editable.

Now, you will be prompted with a screen to save the file with the *.qvw extension. You need to choose a target location for this file to be saved. Click on "Next step" to proceed. Once the file is saved, the script gets executed to load the data into Qlikview.

Now, you can see the data loaded using Table Box Sheet Object. 

Refer to the topic Creating Table Box, to see how to create Table Box and view the loaded data.

2) Delimited File

The data in the form of plain text, where fields and values are separated by comma, tab, or space is recognized as a valid file in Qlikview, for data loading.

For example, CSV (comma-separated values) files can be used for data loading.

You can load a CSV file into Qlikview from Data from file options available in the script editor under the File Menu. Alternatively, you can open a new Qlikview document and click Ctrl + E Keys to open the script editor window, as shown below. Now, choose your file from the appropriate path.

Upon opening the required CSV file, a window pops up as shown below. Under Labels choose Embedded Labels and click Finish.

The loading of the file happens through the load script. For delimited files, you can manually edit the file format in the script.

Now, you will be prompted with a screen to save the file with the *.qvw extension. You need to choose a target location for this file to be saved. Click on "Next step" to proceed. Once the file is saved, the script gets executed to load the data into Qlikview.

Now, you can see the data loaded using Table Box Sheet Object. 

Refer to the topic Creating Table Box, to see how to create Table Box and view the loaded data.

3) XML File

The XML file format is most commonly used on worldwide webs, intranet, and elsewhere using the ASCII text. It is similar to HTML, where tags define the language. However, in HTML, tags define the structure, while in XML tags define the meaning of the data contained in the file. 

XML stands for Extensible Markup Language (XML). 

Qlikview can use XML files as a data source.

The process of loading data into Qlikview from an XML file is similar to delimited files. 

To begin with, in the menu, click on Insert → Load Statement → Load from File. Now, choose the file you want to load.

On selecting the file you want to upload, a window opens up as shown below. Under the section, File Type, choose XML. The content of the XML file appears in the table with the headers. Now, click on Finish.

Once the file is loaded, the load script window appears. You can edit the script for any customization.

Now, you will be prompted with a screen to save the file with the *.qvw extension. You need to choose a target location for this file to be saved. Click on "Next step" to proceed. Once the file is saved, the script gets executed to load the data into Qlikview.

Now, you can see the data loaded using Table Box Sheet Object. 

Refer to the topic Creating Table Box, to see how to create Table Box and view the loaded data.

4) Web File

Qlikview can process data from the web files present online. You have to specify the URL and Qlikview can fetch the HTML file and process it. Qlikview handles HTML files to analyze the structure of the file and extract the relevant data from it. To do this, choose the Web files option from the Data from files section under the Data tab of Script Editor, as shown below.

Once the web file is chosen, a window appears, with a text box, where you have to input the URL of the web file.

Upon choosing the Web file, the window given below will be seen. It has tables named @1, @2, @3, and so on. Choose the first table and click on Next, twice.

From the table selected, select the columns that are required. You can cross out the unimportant ones. Once done, click Finish.

Once the file is loaded, the load script window appears. You can edit the script for any customization.

Now, you will be prompted with a screen to save the file with *.qvw extension. You need to choose a target location for this file to be saved. Click on "Next step" to proceed. Once the file is saved, the script gets executed to load the data into Qlikview.

Now, you can see the data loaded using Table Box Sheet Object. 

Refer to the topic Creating Table Box, to see how to create Table Box and view the loaded data.

5) Database

Qlikview is configured to connect to the most popular databases like MySQL, SQL Server, Oracle, Postgres, etc. It can fetch tables and structures from the database and store the data in memory for future processing.

The process of connecting to a database includes creating an ODBC connection using DSN and fetching the data using DSN.

For this tutorial's sake, let us learn to connect to a MySQL database. We assume that you have the MySQL environment ready and installed. To create an ODBC DSN, follow these steps Creating ODBC DSN. Rename the DSN to mysqluserdsn or you can retain the default one.

Connecting to the Database

For the tutorial's sake, we will use the inbuilt database of MySQL called "sakila". 

Now, open a new Qlikview Document and open the script editor (Ctrl + E keys). Under the tab called "Data", locate a section called "Database". From the options available, choose ODBC and click Connect. This opens a window (shown below). Now, choose the DSN-created mysqluserdns and click Test Connection. The success message called the message Connection Test succeeded should appear.

Once the connection is successful, the screen given below appears. It shows the connection to DB in the main window.

Click Select in the above window, to get a list of tables and columns.

Once we click OK, you will get back to the window with a script for the selected table.

The data loaded in Qlikview needs to be permanently stored for future analysis. To do this, we need to store the file in .qvd format. Now, open the script and edit into the following code. Here the path is edited along with the name of the tables and the columns. Now, this will be saved in .qvw format.

Press Ctrl + R Keys to reload the .qvw file generated and click on Next in the chart wizard.

You will now see the chart with the data from the selected table.

6) Inline Data

The data can be loaded into Qlikview by typing or pasting data into it. This is one of the direct ways of loading data from the clipboard. This option is available in the script editor, under the Insert tab. This method of data loading is called Inline Data Loading.

Script Editor: To open through Inline Data loading, in Script Editor, navigate Insert → Load Statement → Load Inline.

Inserting Data

On clicking on the above option, a window with a spreadsheet-like document opens. Here you can type in the data to copy-paste from the clipboard. 

Note: the column header is created automatically.

Now, click on Finish.

The data in the above document is loaded into Qlikview through the loader script. This can be viewed on Script Editor.

Table Box Object: On creating the Table box sheet object, all the data loaded will be displayed. We see that this data is read from the Inline Data Load option.

7) Resident Load

Sometimes when you want to create a table, deriving data from an already existing table in the same script, you can use Resident Loading. This is the type of loading where, the data from an already existing table in RAM are processed by a script.

Note: Here the new table and the existing table must be present in the same script.

The picture given below is an example of the script of Resident Load.

8) Preceding Load

This is a type of data loading, where a loaded statement takes column names from another load statement present in the same script. The load statement from which column names will be borrowed will be at the bottom of the editor window, while the data reading load statement will be placed at the top.

Load Script: The loaded script shown in the window is an example of Preceding Load. The script is loaded as Inline data and the max function is applied to one of the columns. 

The load statement at the bottom makes the data available in Qlikview's memory, which can be used by the load statement at the top.

On creating the Table Box Sheet Object, the loaded data can be viewed.

9) Incremental Load

With the increase in the number of data that the Qlikview processes, the time-taken increases, and the activity rate decreases. To deal with this issue, Qlikview will take up the updated or newly added records from the source. This process of data loading is called Incremental Load.

The process of identifying the new record or updated record is done using a sequential unique key or date-time stamp. These values have to flow from the source files to Qlikview's document.

10) Data Files (QVD)

One of the distinguishing features of Qlikview is to store massive data in compressed form. For any Qlikview document, you need not connect it to a data source and store it. The data will already be stored along with the layout of the document in .qvd format. QVD is a flat-file format with a .qvd extension. A QVD file stores data for one document in Qlikview and is created using the script editor of Qlikview.

Advantages of QVD files

1. Helps generously in scaling up of data
2. Improves the speed of data loading
3. It helps in clubbing data from multiple data sources into one data set.
4. Assists in parallel data extraction.

Explore - Advantages of QVDs

Creating QVD files

A Qlikview document can be stored in QVD format, using the STORE statement during the loading of the document. This creates a .qvd file in the same location as the document and will be separate from the QVW file, through which we create this.

Using QVD files

A QVD file is loaded into a Qlikview document in a similar way as that of CSV, Excel, and delimited files. Click on the Open option under the File and select the required QVD file. Upon opening the file, you can select the columns, see the data and perform the required data transformation. 

QVD Script Loader

On clicking Finish, you will be presented with a window containing the script. You may want to edit this code. Upon doing changes, click OK.

Displaying data from QVD

Once OK is clicked, reload the document using Ctrl + R Keys. When the Window appears, choose Layout → New Sheet Objects → Table Box. A window with all the columns of the table appears. Select Add All. You may use Promote/Demote to perform a change in the order of columns. Click Finish. 

You will now see the screen with the content of the QVD file.

Creating Table Box

Table Box is a Sheet object that displays the data in the tabular format. You can create a table box by Layout → New Sheet Object → Table Box.

Click Next to choose fields for Table Box, from the options available. Promote or Demote options can be used in re-arranging the fields.

Table Box Data

This completes the data loading process. Now, you can view the Table Box with the loaded data.

Qlikview Report Interface

Sheets and Objects

Every Qlikview document is made of a compulsory worksheet called Main. However, any number of additional sheets to the Qlikview Document.

Each Sheet can have multiple sheet objects, charts, and tables. The objects in a sheet can be re-arranged using Promote Sheet/Demote Sheet option and the sheet can be removed off, along with its objects using the Remove Sheet option.

Sheet Properties

The properties of Sheets include customizing the sheet with the title and colors. For example, right-click anywhere on the sheet and choose the Properties option to customize the following:

1. Sheet Settings → Color: This option is used to change the background color
2. Tab Settings → Custom Colors: This is used to set the color of the tab, where the sheet name appears.
3. Title: Used to set the title of the sheet.

Creating Sheet Objects

These are the data elements, embedded in the Sheets, which represent the data loaded in Qlikview's memory. Each Sheet object is attached to a data source with one or more its columns.

The above image shows the creation of Sheet Objects from the Layout Menu.

Sheets objects are arranged in a logical way in a sheet. Each object is associated with the other within a sheet. Every object displays data from a data source.

Scripting

Scripting is the method to operate on data in the Qlikview database. It is a very powerful feature, which allows controlling the data loading and data transformation functions in Qlikview. It uses many built features of Qlikview to handle data.

Scripting in Qlikview is done through the scripting interface or the editor, which is accessed from the File menu using Script Editor. A shortcut key is Ctrl + E Keys.

Features of Script Editor

The Script Editor has a different menu from the main menu. Various features of this interface can be accessed using this menu. These features are given below:

File Menu

1. Reload: reloads the script to fetch new data if any.
2. Upper/Lower Case: Converts the case of the Qlikview data, as Qlikview is case sensitive.
3. Comment: Comments the blocks of code.

Edit Menu

1. Clear Entire Script: clears the code in the active script tab.
2. Open Script File: used to open the saved script files.
3. Table Viewer: used to see the data in tabular format.

Insert Menu 

1. Environment Variables: used to insert the standard set of environment variables.
2. Script Files: used to browse for required script files and inserts them.
3. Connect/Disconnect Statement: connects or disconnects from external databases.

Tab Menu

1. Insert Tab: used to insert a Tab at the current cursor position.
2. Promote/Demote Tab: used to move the tabs from left to right and vice versa.
3. Merge with Previous: merges the content of the active tab with the previous tab.

Tools Menu

1. ODBC Administrator 64 bit/ODBC Administrator 32 bit: sets the correct DSN information for data sources.
2. Editor Preferences: used to configure the text font and size, help features, shortcuts, default-scripting engine, etc.
3. Syntax Check: Validates the syntax of the script code.

Dashboard

The dashboard is the user interface where multiple fields are displayed to the user. The associative memory of Qlikview presents dynamic values in all sheet objects.

Dashboard Components

The dashboard is made up of various components. Some of them are:

1. Charts
2. Input Boxes
3. Selection Boxes
4. Buttons
5. Notes & Comments
6. Sheet objects like bookmark, container, search, line/arrow object. 

Qlikview Charts

There are various types of charts available in Qlikview to represent different kinds of data sets. A chart is a graphical representation of data in the form of a diagram. Different types of charts supported in Qlikview are:

1. QlikView Block Chart
2. QlikView Funnel Chart
3. QlikView Radar Chart
4. QlikView Grid Chart
5. QlikView Line Chart
6. QlikView Mekko Chart
7. QlikView Scatter Chart
8. QlikView Gauge Chart
9. QlikView Pie Chart
10. QlikView Bar Chart

Out of these charts, Pie charts and Bar charts are the most widely used charts. To create any chart, a few common steps are followed. Below is a brief description of the steps to create a chart in general.

1. Have a file of Input data in the form of Excel, XML, delimited, or any other kind.
2. Load the script into Qlikview (You can refer to the data loading section of this tutorial)
3. Create a Table Box to view the loaded data in a tabular form.
4. Open the Quick Chart Wizard and select the type of chart from the list.
5. Choose the chart specifications. For example, char dimension for a bar chart.
6. Choose the chart expression, where the expression defines the function which the chart represents. It may be the Sum, Average, Max, etc.
7. Opt for any more details if prompted, according to your need. Once done, the chart will be created.

Qlikview Boxes

Under this topic, let us learn about the List Box and Multi Box.

List Box

List box represents all the values associated with a field in Qlikview. If you highlight the value in the list, related values in the other sheet objects get highlighted. This feature of auto highlighting enables better visualization and analysis. It also helps to trace down the path among various objects in the sheet. The list box also allows a search.

A list Box can be created through the layout menu like this: 

Layout → New Sheet Object → List Box

IntervalMatch

IntervalMatch is a special feature of Qlickview, which is used to match distinct numeric values to numeric intervals. It helps to analyze the planned events vs. the performed events.

The application of IntervalMatch can be seen below:

Documents

Documents are the files in the Qlikview that contain everything used for proper data analysis and representation. It consists of sheets, variables, data models, source-data connection details, etc.

Properties of Documents

Proper information about Documents can be obtained through this:

Help → document Support Info. The picture given below shows a sample output.

Scheduling a Document

Documents have a special feature in Qlikview, which is scheduling. A document can be scheduled to reload at desired intervals. This option is available under the Schedule tab of document properties. 

Qlikview Data Model

The concept of the Qlikview Data Model is a very broad one. Qlikview provides its users, various methods to perfect their data model. Some of the key data model concepts are discussed below.

1) Generating Data

Many times while performing data analysis, data scientists would want some data that is generated programmatically and is not from any external data source. For example, a set of random numbers generated or the list of all the leap years. To address this need, Qlikview has come up with a feature called Autogenerate, which generates data automatically.

The picture given below is an example of the Autogenerate application.

2) Synthetic Key

A synthetic key is a unique key generated by Qlikview when there is an ambiguity to find a unique key between 2 tables. This scenario comes into the picture when the two tables have two or more fields in common. The associative memory of Qlikview detects this scenario and automatically creates another table to hold the value of the new key generated.

3) Dimensions and Measures

Dimensions and Measures are the fundamental entities in data analysis. Dimension can be defined as a descriptive field in the data set which represents a few discrete values. Measures can be defined as a numeric field, which calculates a specific value for each dimension.

This is used in data analysis in the following way. For example: if you want to analyze this: “what is the percentage change in the volume of sales for each quarter?” 

Here, each quarter represents the Dimensions, which is the name of the quarter. The percentage change in volume describes the Measures, which is a computation of value with respect to each value in the dimension. 

In Qlikview, the dimensions and measures can be applied to tables and charts.

4) Star Schema

A star schema data model denotes a case where multiple dimensions are linked to a single fact table. In bigger (higher) models, multiple fact tables can be linked with multiple dimensions and other fact tables. The basic advantage of this data model lies in faster query processing and reducing the number of joins among tables.

The fact table usually contains data, which are Measures, having numeric values. All the calculations will be performed on the fields in the fact table. 

The unique keys in the Dimension tables are used to map to the fact table, where the key will usually have the same field name. Hence, the fact table contains the keys from the Dimensions table and forms a concatenated primary key, which will be used in various queries.

Qlikview Data Analysis

Data analysis is one of the most important functionalities of Qlikview. There are many tools used to perform data analysis in Qlikview. Some of them are described below.

1) Joins

Joins are performed to combine data from two data sets into one, depending on a certain condition. Only the column and row values that satisfy the condition are filtered into the output. The joins in the Qlikview function in the same way as the joins in the SQL.

The joins are written in the scripts and employed on the data set to perform the required operation on the data.

Let us see different types of joins performed in Qlikview.

a) Inner Join

This Join fetches only the common rows between two tables.

b) Left Join

This Join fetches all the rows of the left table and only the matching rows from the right table.

c) Right Join

This Join fetches all the rows of the right table and only the matching rows from the left table.

d) Outer Join

It fetches all the rows from both tables.

In order to perform each of the joins, 

• First, load the data file into Qlikview.
• Open the script editor and edit the script with the code for each join.
• Save the above work.
• Open the result in a new Table box.

2) Tables

Tables are used to arrange data in an orderly way. In Qlikview, tables help in a systematic data analysis, by presenting the data in an organized fashion. There are different types of tables available in Qlikview, for various situations. Some of them are discussed below:

a) Cross Tables

While dealing with data, many times, we need to have tables transposed, where the columns become rows and vice versa. This is carried out by rolling up many columns together or repeating many values in a row to achieve the desired layout.

To perform cross tables, 

• First, load the data file into the file wizard by choosing the right file type.
• Once the file is loaded, click on Next in File Wizard to get the screen below.
• In the next window, File Wizard → Options, click on the Crosstable button. The columns are seen highlighted in different colors. The pink color shows the qualifier field, which will be repeated across every value of the Attribute Field. Click OK.
• The transformed data has all the Region fields clubbed into one column, with the values repeating after each quarter.
• The script of the cross table transformation looks like below.
• Create a table box sheet to see the transposed table.

b) Straight Tables

These are simple tables with powerful features like column rearrangement, background color change, and sorting.

c) Pivot Tables

Pivot tables are regularly used in data analysis, to present the sum of values across many dimensions present in the data. Qlikview's Chart option allows you to create a Pivot Table, by choosing the relevant chart type.

d) Mapping Tables

As the name suggests, the mapping table is created by mapping the column values between two tables. It is also called the Lookup table. It is generally used to check for a particular value from some other table.

3) Set Analysis

The set analysis feature of Qlikview is used to segregate data from different sheet objects into many sets and maintain some of the values unchanged. The default behavior of sheet objects in Qlikview lies in the associative property. 

The set analysis creates an option to not associate some of the sheet objects with others. This helps in data filtering in some sheet objects and seeing the corresponding effect in the other, while the sheet object chosen as a different one, retains its own values.

This dissociation is possible, as a clone of the original data set is created and is worked on for set analysis.

4) Master Calendar

In Qlikview, sometimes, we need to create a calendar reference object, which can be linked to any data set present in Qlikview's memory. This master calendar generated will supply the missing calendar data, which might be required by the data set for further data analysis.

Qlikview Data Transformation

Data transformation involves changing the original data in a data set to a different format. This includes adding, removing, or filtering the existing data. The data transformation is carried out by Qlikview using built-in functions. Some of them are discussed below:

Transformation Wizard

This is an option for data transformation in Qlikview. It modifies the data in Qlikview's external memory which is loaded from an external source. It is the transformation step from ETL (Extract, Transform, Load) process, which can perform functions like deletion (Garbage), addition or fills, column, unwrap, rotate, etc. on the data.

Fill function

The fill function is used to fill or add the values from existing fields into another field. In Qlikview, the fill button of the Fill tab gives the option to perform adding values into the empty cells. You can also specify fill conditions and strategize the filling of cells in Qlikview.

In the transformation wizard, select the type of transformation to Fill, to proceed with the transformation.

Column Manipulation

This is a type of data transformation, where, a new column is populated with the values from an existing column, upon meeting certain criteria. This criterion can be an expression, created as a part of the data transformation step.

Rotating Table

A rotating table is a concept similar to the matrix transpose function. There are different ways of transposing a table, which would yield different results. 

It can be useful in data modeling to swap different rows and columns. The rotating table function is similar to the cross tables in Qlikview, but rotating doesn’t allow for the aggregation of column data.

The option to rotate tables is available in the File Wizard, as shown in the picture below. Upon selecting the rotate tab, you can choose the option to transpose to get a transformed table. The result can be viewed in a table box.

Conclusion

Qlikview Tutorial is a data visualization and analysis tool that is built robustly to perform all the functions efficiently. Many use cases are considered to develop the software, which spans all the possible edge cases to perform thorough data visualization and analysis. Any enterprise can start using Qlikview for a better BI experience while dealing with data of any size.

Check out other popular BI Tool Tutorials 

• Tableau Tutorial
• Qlik Sense Tutorial
• Power BI Desktop Tutorial