Qlik Sense Cookbook

4.2 (10 reviews total)
By Philip Hand , Neeraj Kharpate
  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Getting Started with the Data

About this book

This book is an excellent guide for all aspiring Qlik Sense® developers. It will take you through the basics, right through to the use of more advanced functions. With the recipes in this book, you will be empowered to create fully featured desktop applications in Qlik Sense®.

Starting with a quick refresher on obtaining data from data files and databases, this book moves on to the more refined features of Qlik Sense®, including visualization, scripting, and set analysis. The tips and tricks provided will help you to overcome challenging situations while developing your applications in Qlik Sense®. This and more will help you to deliver engaging dashboards and reports efficiently.

By the end of the book, you will be an expert user of Qlik Sense® and will be able to use its features effectively for business intelligence in an enterprise environment.

Publication date:
November 2015
Publisher
Packt
Pages
290
ISBN
9781782175148

 

Chapter 1. Getting Started with the Data

In this chapter, we will cover the basic tasks related with extracting data into a Qlik Sense application:

  • Extracting data from databases and data files

  • Extracting data from Web Files

  • Activating the Legacy Mode in Qlik Sense® desktop

  • Extracting data from custom databases

  • Invoking help while in the data load editor or the expression editor

  • Previewing data in the Data model viewer

  • Creating a Master Library from the Data model viewer

  • Using a Master Library in the Edit mode

 

Introduction


Data is the core aspect of any Business Intelligence application. It provides information that helps organizations to make decisions.

A Qlik Sense application is based on the data extracted from various sources, such as relational databases, CRM systems, ERP systems, and data files.

This chapter introduces the user to various methods of extracting data into a Qlik Sense application effectively. It is assumed that the reader is already acquainted with the concepts of ODBC, OLEDB, and relational databases. The chapter also provides an essential recipe for fetching the data into Qlik Sense from a SAP system. The SAP connector can be downloaded from the Qlik website and installed before working on the recipe. You need to acquire a valid license enabler file beforehand, in order to download the SAP connector.

The later part of the chapter focuses on a few recipes regarding the creation of a library and content.

 

Extracting data from databases and data files


The data within an organization is usually stored in relational databases and data files. Extracting data is the first step towards creating a data model. The following section demonstrates the steps to extract data from an MS Access database and a delimited (.CSV) file. The procedure to extract data from other relational databases is the same as the process for extracting data from MS Access.

The dataset that we will use is available publicly and covers information about routes and fares of various transport systems in Hong Kong. The original data files have been downloaded from (https://data.gov.hk/) website. This dataset can also be obtained from the Packt Publishing website.

The data connections in the Qlik Sense data load editor save shortcuts leading to commonly used data sources, such as databases and data files. The following types of connections exist in Qlik Sense:

  • ODBC database connection

  • OLEDB database connection

  • Folder connection

  • Web file connection

This recipe deals with the ODBC, OLEDB, and Folder connections. The web file connection will be dealt with in a separate recipe.

Getting ready…

The dataset required for this recipe that is downloaded from the Packt Publishing website comes in a zipped folder called as QlikSenseData. Extract all the files from this zipped folder and save them on the hard drive at a desired location.

If you are connecting to the database using Open Database Connectivity (ODBC) then:

  1. Install the relevant ODBC drivers on your system.

    Note

    For the sake of our exercise, we need the MS Access drivers. The system DSN connection can be set up through the ODBC administrator under the Administrative Tools in Control Panel.

  2. While setting up the ODBC connection, select the ROUTE_BUS.mdb file as the Data Source from the QlikSenseData folder.

  3. Name the ODBC DSN connection as HongKong Buses.

  4. Create a new Qlik Sense application and open the data load editor.

  5. Click on the Create New Connection and select ODBC.

  6. Select HongKong Buses under System DSN.

  7. Name the data connection as Qlik Sense CookBook ODBC.

  8. The following image shows the details we enter in the Create new connection (ODBC) window:

If you are connecting to the database using OLE DB connectivity, we can directly set this up through the editor:

  1. Open the data load editor in Qlik Sense.

  2. Click on the Create New Connection and select OLE DB.

  3. Select the Microsoft Jet 4.0 OLE DB Provider (32 Bit) driver from the provider drop-down list.

  4. Insert the Data Source file path, which in our case will be the path for the ROUTE_BUS.mdb file in the QlikSenseData folder.

  5. Name the data connection as QlikSense CookBook OLE DB.

  6. The following image shows the details we enter in the Create new connection (OLE DB) window:

If you are extracting the data from a data file, such as .CSV, perform the following steps:

  1. Open the data load editor in Qlik Sense.

  2. Click on Create New Connection and select Folder.

  3. Select the location of the QlikSenseData folder which contains our data files. Alternatively, one can directly enter the path of the source folder under Path.

  4. Name the data connection as Qlik Sense CookBook Data.

  5. The following image shows the details we enter in the Create new connection (folder) window:

  6. Once the connections are created in the Qlik Sense library, they will be seen as a list under Data connections in the data load editor, as shown in the following screenshot:

How to do it…

If you are working with an ODBC or an OLEDB data connection, follow the steps:

  1. Insert the relevant data connection string to the script by clicking on Insert connection string, as shown in the following screenshot:

  2. Next, click on Select data under Data connections to view and extract data from the ROUTE table in the MS Access database, as shown:

  3. The preview of the ROUTE_BUS.mdb table will look like the following. The fields in the table can be excluded or renamed while working in the Preview window, as shown in the following screenshot:

  4. Click on Insert Script in the Preview window. This will insert the connection string as well as load the statement to the script. Make sure that you delete the duplicate LIB CONNECT TO 'Qlik Sense CookBook ODBC'; statement from your script.

  5. Load the data in your application by clicking on the button.

Keep the Close when successfully finished option checked in the data load progress window. If the data is loaded successfully, then the window automatically closes or else the error encountered is highlighted.

  1. On a similar note, in order to test the Qlik Sense data files, Click on the Select data option under the Qlik Sense CookBook Data connection.

  2. Next, select the ROUTE_GMB.csv file from the QlikSenseData folder and load it in the application.

  3. The preview of the ROUTE_GMB.csv table will look like the following screenshot. Make sure that you select Embedded field names under Field names. Note that the Delimiter in this case is automatically set to Comma.

  4. Insert the script and then save and load it.

How it works…

The LIB CONNECT TO statement connects to a database using a stored data connection from the Qlik Sense library; thus, acting as a bridge between our application and the data source.

There's more…

This recipe aimed at extracting data from common data sources, such as RDBMSs and data files. Qlik Sense can also extract data from web files and custom data sources such as SAP. We will see this in the forthcoming section.

See also…

  • Creating a Master Library from the Data model viewer

 

Extracting data from Web Files


Often, the data required for the purpose of reporting is not stored in a database, but instead needs to be fetched from a website. For example, customer location information specifically the geographic co-ordinates used in mapping analysis is not available internally within an organization. This information may be available on the web and can be extracted from there.

Getting ready…

When extracting the data from a web file:

  1. Open an existing Qlik Sense application or create a new one.

  2. Open the data load editor.

  3. Click on Create New Connection and select Web file.

  4. The Select web file window will open.

  5. Insert the following URL from which you can fetch the data:

    http://www.csgnetwork.com/llinfotable.html
  6. Name the connection as QlikSense Cookbook Webfile, as shown:

How to do it…

  1. In the list under Data Connections, select QlikSense Cookbook Webfile and click on Select Data. This will open up a preview window listing out all the tables from the web page. When you carefully examine the table contents, you realize that it is the second table @2 that contains the location information.

  2. Check the box next to @2 and ensure that it is selected, so the correct table is shown in the preview. The user will need to change the value under Field names to embedded field names.

  3. The preview of the table will look like the following screenshot:

  4. Select all the fields from the table in the preview window. Click on Insert script to load the web data in the application.

  5. Name the table as Country_Location and the script will read as follows:

    Country_Location:
    LOAD
    Country,
    Capital,
    Latitude,
    Longitude
    FROM [lib://QlikSense Cookbook Webfile]
    (html, codepage is 1252, embedded labels, table is @2);
  6. Save and load the data. Once the script is successfully loaded, the data model viewer will show the loaded table.

How it works…

Qlik Sense connects to the web file using the stored data connection. Once connected it identifies the tables in the HTML source and lists them in the preview window.

Certain external websites require authentication in order to be accessed and Qlik Sense is unable to cope with websites that are secured in this manner. In order to get over this issue, we can use a third party data extraction tool. The extracted data can be stored in a data file, such as a qvd. The qvd file can then be used as a data source in the Qlik Sense application.

There's more…

Qlik Sense can also extract data from other data formats, such as XML. The underlying principles remain the same as explained in the preceding recipes.

See also…

  • Creating a Master Library from the Data model viewer

  • Activating the Legacy Mode in Qlik Sense® desktop

 

Activating the Legacy Mode in Qlik Sense® desktop


Qlik Sense is a developing product; hence, certain features are not active when running the Desktop version in its standard mode. A prime example of this is using the Custom Connect to statement to create the ODBC/OLEDB connection strings or attempting to connect to a custom database as SAP. Both these activities are not possible if Qlik Sense runs in its standard mode. In order to get these functionalities to run, we need to activate the legacy mode. However, one must note that enabling the legacy mode has security implications, if the application is deployed on the Sense server then one does not have control over the data connections in QMC (if the legacy mode is activated). The library security features may also be lost; moreover, the legacy mode does not work with Qlik Cloud either.

Getting ready…

Activating the Legacy Mode requires changing a parameter value in the settings.ini file for Qlik Sense.

How to do it…

  1. Make sure that Qlik Sense Desktop is closed before opening the settings.ini file.

  2. Open the settings.ini file that is by default stored under C:\Users\{user}\Documents\Qlik\Sense\Settings.ini.

  3. Change StandardReload=1 to StandardReload=0.

  4. Save the file and start Qlik Sense Desktop in order to run it in a legacy mode, as shown:

How it works…

Changing the value for the StandardReload parameter in the settings.ini file enables the Legacy Mode in Qlik Sense. When running in the Legacy mode, any of the scripts in Qlik View can be directly used in Qlik Sense. This will also allow us to use the library connections.

There's more…

The Qlik Sense has the capability to use the same script that is found in any Qlikview file. One can also use a binary load statement in Qlik Sense in order to load the entire data model from an existing Qlikview file. All the Custom Connect To statements can only be used after we activate the legacy mode.

See also…

  • Extracting data from custom databases

 

Extracting data from custom databases


The current version of Qlik Sense does not support the loading of data from custom databases, such as SAP or Salesforce. Nevertheless, it can still be achieved in a few simple steps. The following recipe explains the steps to load data from a SAP database.

Getting ready…

The Custom connector option under Create new connection is not available in the Qlik Sense data load editor. This feature is going to be introduced soon in a forthcoming release of the product.

The following recipe requires you to use another Qlik product named Qlikview in order to generate the extract script that is to be copied and used in the Qlik Sense application. Qlikview is free software that can be downloaded from the Qlik website. The recipe also requires the SAP connector for QlikView to be installed.

How to do it…

Once we install the SAP connector, the RELOADSAPDD.qvw and ScriptBuilder.qvw files are saved on the hard drive.

We will work along with the RELOADSAPDD.qvw file, which is stored at the C:\ProgramData\QlikTech\CustomData\QvSAPConnector\ScriptBuilder location.

In order to extract data from a custom database, such as SAP:

  1. Activate the legacy mode as described in the recipe just prior to this.

  2. Open the Qlikview file and input the SAP credentials to generate the connection string similar to the following:

    CUSTOM CONNECT TO ""Provider=QvSAPConnector.dll;ASHOST=192.168.210.166;SYSNR=00;CLIENT=100;KeepCasing=1;NullDate=1;XUserId=UPJDRIRJJaSMVEVIXSFA;XPassword=IQWOQIRNJbaMXUVMXLMGSEA;"";
  3. Open Qlik Sense. Copy and paste the SAP Connection string from the script editor of the QlikView file to Qlik Sense.

  4. Similarly, one can copy and paste the load script generated for any SAP table in a QlikView file to a Qlik Sense file.

  5. Save and load data.

  6. The data load editor with all the connection strings will appear, as shown in the following:

How it works…

The essence of the recipe is that the custom connections don't work in Qlik Sense, unless it is running in a Legacy mode. The user can copy the script generated in the QlikView file to the Qlik Sense Load script while running the application in the legacy mode, as this script cannot be generated directly in Qlik Sense.

There's more…

Qlik Sense can extract data from any data source that can be loaded by QlikView (such as Salesforce) in practically the same way as it is described in this recipe.

See also…

  • Activating the Legacy Mode in Qlik Sense® desktop

 

Invoking help while in the data load editor or the expression editor


As a Qlik Sense developer, one often needs access to the help module in order to search for certain functions or simply understand their usage and syntax in detail. Help is available in the dropdown menu on the toolbar. However, when we use this option, it takes us to www.help.qlik.com/sense and then we again need to search for the keyword. It's not a huge effort but it would be more beneficial if we were taken directly to the information regarding the keyword or function we are looking for.

Getting ready…

For this recipe, we will use the Automotive.qvf file, which comes as a built in example when we install the Qlik Sense Desktop.

How to do it…

  1. Open the Automotive.qvf file from the Qlik Sense desktop hub.

  2. Open the data load editor and go to the Territory data tab.

  3. Click the Help () button inside the data load editor. This will highlight the script so that all the keywords are then clickable links.
  4. Click on the keyword pick in the script. This will take us to the correct place in the help file, as shown:

There's more…

An alternative approach that can be used in Qlik Sense versions prior to 2.0.1 is as follows:

  1. Highlight the key word pick in the script.

  2. Press ctrl + h. This will take you directly to the content explaining Pick on the help page.

A list of useful shortcuts for Qlik Sense is given at the end of this book.

See also…

  • Keyboard shortcuts in Qlik Sense® desktop

 

Previewing data in the Data model viewer


As any experienced Qlik developer will tell you, the data model viewer is a key component you will undoubtedly spend time using on your Qlik journey. Qlik Sense has brought with it some nice new features. We will also delve into the different insights that can be gleaned from the data model viewer:

Getting ready

For this recipe, we will make use of the Data model viewer.qvf application. This file is available for download on the Packt Publishing website.

How to do it…

  1. Open the Data model viewer.qvf application that has been downloaded from the resource library.

  2. Click on data model viewer in the Navigation dropdown on the toolbar.

How it works...

In this section we will see how the different types of data are viewed.

Viewing the data model

The data model consists of a number of tables joined by the key fields. The following screenshot contains functions that can be used to manipulate the layout of the data model:

The detail of the available keys (from right to left) is given as follows:

  • Collapse all: This reduces down the tables to just their headers; thus, hiding all the fields

  • Show linked fields: Expands the tables enough to only display the key fields in each

  • Expand all: Displays all the fields for each table

  • Internal Table viewer: Shows the internal representation of the data model

  • Layout: Provides options to auto align the table grid or space out across the screen

  • Preview: Toggles the data preview screen to either on or off

Viewing the associations

Clicking on a table will highlight its associated tables in orange. The customer's table is selected in the following screenshot and the shared key here is Address Number:

Click on the CustomerAddress table to see a highlighted expansion, via the state key, as shown:

Table Meta Data

The data model viewer also provides information on the contents of each table.

Click the header of the customer address table then open the Preview pane by clicking the Preview button in the bottom left hand corner.

The following preview will be displayed at the bottom of the screen:

Along with a small snippet of the table contents, the far left table also provides some high level table information about the number of rows, fields, keys as well as any tags.

Next, click the Address Number field from the Customers table in the data model viewer.

You can now see more detailed information about the individual field.

These are:

  • Density

  • Subset ratio

  • Has duplicates

  • Total distinct values

  • Present distinct values non-null values

  • Tags

This information is very helpful when we are debugging issues. If a count does not return the expected result, you may want to ensure that there are no duplicates.

If a selection is not filtered correctly you may want to check the sub-set ratio of the key and so on.

There's more...

Double clicking a table header in the data model viewer will either collapse or expand the table fully.

 

Creating a Master Library from the Data model viewer


To help reduce the repetition and developer error, Qlik has introduced a master library where we can store reusable items, such as dimensions, measures and even whole visualizations. For people experienced in Qlik's other products such as QlikView, just think; "no more linked objects and storing expressions in variables!"

It is easy to think of library items in a self-service context. Don't get me wrong; ultimately you will have to decide what will be published; from your data model to the world for their own analysis purposes. Having said that, the secret sauce of this recipe is in saving your own time.

It is a productivity hack that implies; "automation is to your time what compound interest is to money". While it is not an exact parallel, this is a nice concept to frame the usefulness of timesaving functions in Qlik Sense. The effective use of the library saves time spent on scrolling down field lists, rewriting expressions over and over, applying a single change in multiple places, and so on.

Once you have saved enough time to eclipse the setup investment, the value of taking this approach can only compound with continuous development.

Getting ready

  1. Create a new Qlik Sense application and name it Master Library.

  2. Open the data load editor.

  3. Enter the following script and load the data by clicking on the button. (The script is available in a separate text file that can be downloaded from the Packt Publishing website):
    Data:
    LOAD * INLINE [
        Name, Region, Country, City, OrderId, Sales, Company, OrderDate
        Wooten, C, Mozambique, Carmen, 1, 45.55, Est Nunc Laoreet LLC, 22/12/14
        Blankenship, Delta, Cayman Islands, Sapele, 2, 95.76, Lorem Donec Inc., 17/01/15
        Sheppard, Wyoming, Vatican City State, Cheyenne, 3, 38.31, Lobortis, 07/08/14
        Goddard, H, Curaçao, San Francisco, 4, 86.33, Non Inc., 07/09/14
        Galloway, Aragón, Trinidad & Tobago, Zaragoza, 5, 85.80, Diam Proin., 21/01/15
        Kirsten, Tamil Nadu, Wallis & Futuna, Neyveli, 6, 28.47, Mollis Non Limited, 03/05/14
        Holland, Cartago, Falkland Islands, San Diego, 7, 1.34, Ullamcorper Inc., 17/07/14
        Thaddeus, BC, Canada, Oliver, 8, 59.04, Ante Nunc Mauris Ltd, 17/02/15
        Lareina, CA, Spain, San Diego, 9, 4.55, Pellentesque Tincidunt Limited, 29/07/14
        Jescie, Vienna, Monaco, Vienna, 10, 54.20, Ultricies Ligula Consulting, 16/06/14
        Logan, IL, Saint Barthélemy, Paris, 11, 91.31, Mi Foundation, 13/12/14
        Shannon, CG, Nepal, Aberystwyth, 12, 80.86, Auctor Non LLC, 03/05/14
        Andrew, SO, Argentina, Sokoto, 13, 88.78, Scelerisque Mollis Associates, 12/12/14
        Jocelyn, WP, Tanzania, Konin, 14, 15.91, Ligula Tortor Dictum Ltd, 22/08/14
        Gordon, FL, Hong Kong, Miami, 15, 93.97, Suscipit Inc., 12/05/14
    ];

How to do it...

Once the data has been loaded, you can check the results by opening the data model viewer through the navigation dropdown () in the top corner on the left hand side of the toolbar, as shown in the following screenshot:

You can find the Preview button to the bottom left of the screen. There are several other places in Qlik Sense where you can create master library items but the data model preview screen is the best, as it also lets you see the data first. Take a minute to browse the data you have loaded in data model viewer.

  1. In the data model viewer, select the Data table by clicking on its header and then click the Preview button to view the fields and the field values loaded from the Data table.

  2. The Preview window will appear as shown in the following:

  3. Select the Region field from the table to get the preview as shown in the following:

  4. Next, click the button.
  5. The following window appears. If you are likely to publish this dimension for consumption by users, you can enter a description here:

  6. It is advised to use tags to make our life easier. Add the tag Geo and click on .
  7. Now click on to create a Master dimension in the library.
  8. Repeat this process for the Country and City fields.

  9. Click on to go back to the data model viewer.
  10. Finally, it's time to create a measure. Select the Sales field from the Data table in the data model viewer.

  11. Click the button. When we create a Master measure we need to make sure we use an aggregation function such as Sum, Avg, and so on, along with the selected field.
  12. In the Create new measure window, type SUM in front of (Sales), as shown in the following image:

  13. Click on Create.

  14. Save the changes made in the master library by clicking on the button on the toolbar in the table preview. Exit the table preview by going to the App Overview.
  15. Open (or create) a sheet and enter the edit mode by clicking on the button.
  16. Once you are in the edit mode, click the chain () icon on the left hand side of the asset panel to open the Master items menu.
  17. To add visualizations, first create them in the user interface then drag them into the library.

While the Master item menu panel is very useful to speed up the development when defining the contents, it is easier to do it from the filters pane. In short, you can browse the entire contents of your data model and right-click on the most important fields to add the ones that will be frequently used.

How it works...

  1. Right click on a field from the field's pane that you want to add to the master library.

  2. Click on Create Dimension, enter a Description and any relevant Tags, click Done once finished:

There's more…

We can also create Master dimensions and measures through the GUI. In order to do this:

  1. Open an existing sheet or create a new one.

  2. Click on the Master items icon.
  3. Click on either Dimensions or Measures. This will enable an option to create new library items.

 

Using a Master Library in the Edit mode


As mentioned in the previous recipe, a great benefit of creating a master library is to save you time and reduce the complexity by applying global changes to your visualizations.

There are three main areas in the asset panel when editing a Qlik Sense sheet (Objects, Fields, and Master items). Clicking the chain button opens the Master items pane.

From here, you can manage every aspect of the Master items, such as renaming, replacing, deleting, and editing.

Getting ready

You can continue to use the application from the previous recipe.

  1. If you have not completed the previous recipe. Load the following in your data load editor:

    LOAD * INLINE [
    Country, Area, Sales
    USA, North, 1000
    USA, North, 1200
    USA, South, 2500
    USA, South, 2500
    UK, North, 1000
    UK, North, 2500
    UK, South, 2000
    UK, South, 1900
    ];
  2. Add Country and Area as Master dimensions both with the tag Geo.

  3. Add Sales as a Master measure.

How to do it...

  1. Open the App overview screen by clicking on the navigation dropdown on the toolbar at the top.

  2. Create a new sheet or open an existing one.

  3. Enter the edit mode by clicking on the button.
  4. Click on the object pane button and double click on the bar chart button. .The chart will be added to the main content area automatically.
  5. Type Geo in the search box of the asset panel on the left of your screen. While there are no charts called Geo, the search has flagged up our two tagged dimensions in the master library pane with a yellow circle like this .
  6. Next, drag the Area field to where it says Add dimension. Repeat the steps where the Country field selects Add "Country" when prompted, as shown:

  7. Clear your search on Geo by pressing the button.
  8. Click on Measures.

  9. Drag the Sales measure from the asset panel over to the add measure area of the chart. Voila! You have created your first visualization using Master dimensions and measures:

  10. You can now drag this chart into the asset panel and it will become a master visualization.

There's more…

If you delete a Master dimension or Master measure, the visualizations that use the deleted Master item will not work unless you replace it with a new dimension or measure. The same applies to deleting a field from the data model; the reference will remain a part of the Master item pane until it's updated from the edit screen.

Echoing a comment in the previous chapter regarding time saving and creating Master measures, replaces the need to write expressions as variables for reuse. Another piece of QlikView functionality that has been replicated and expanded upon is the concept of linked objects. Any updates you make in the Master visualization area will be applied globally.

If you rename a field in your script without moving the position it will be applied automatically to all the objects.

About the Authors

  • Philip Hand

    Philip Hand is a Senior Business Intelligence consultant who has worked with QlikView in the BI space for over 7 years. Working with Qlik's top partner in the UK, he has implemented end-to-end solutions in a wide variety of enterprises and for large business customers. Philip has great experience and knowledge working across many sectors from the public sector, insurance, and financial services to large retailer and manufacturing businesses.

    Browse publications by this author
  • Neeraj Kharpate

    Neeraj Kharpate works as a Senior Business Intelligence consultant at Capventis, a QlikView Elite Partner. Having started his career working as a technical support executive for an aviation ERP, Neeraj embarked on his journey with Qlikview in mid-2007. He is a certified Qlikview designer, developer, and trainer. Neeraj has been closely following the developments in Qlik Sense ever since it was launched in 2014 and is extremely excited about the future of this product. Neeraj has completed his Master’s in Business Administration from the Norwich Business School, University of East Anglia. He also has a diploma in Management Consulting from the Chartered Management Institute, UK.

    Browse publications by this author

Latest Reviews

(10 reviews total)
Ebook was easy to order and contained valuable tips.
Printed in colour would be nice.
Excellent