In this chapter, we will cover the basic tasks related to importing data into a Qlik Sense application:
Data is the core aspect of any Business Intelligence (BI) 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, OLE DB, and relational databases. The chapter also provides essential recipes to extract data from web services with the REST connector and FTP data sources with the native Web File connector.
The latter part of the chapter focuses on a few recipes regarding the creation of a library and content and the new features of visual data transformation available in the data manager.
The data within an organization is usually stored in relational databases and data files. Extracting data is the first step toward creating a data model. This 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 for various transport systems in Hong Kong. The original data files have been downloaded from the 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:
This recipe deals with the ODBC, OLE DB, and Folder connections. The web file connection and REST Connector will be dealt with in a separate recipe.
For the following connections, here is a short description, but we don't have recipes for them in this book:
With the exception of Qlik GeoAnalytics Connector, all connectors are free to use. GeoAnalytics Connector requires the purchase or subscription of a separate license from Qlik.
The dataset required for this recipe that is downloaded from the Packt Publishing website (https://www.packtpub.com/big-data-and-business-intelligence/qlik-sense-cookbook-second-edition) comes in a zipped folder called QlikSenseData
. Extract all the files from this zipped folder and save them on your hard drive at the desired location.
If you are connecting to the database using Open Database Connectivity (ODBC):
For the sake of this exercise, we need the MS Access drivers. The system DSN connection can be set up through the ODBC administrator under Administrative Tools
in Control Panel
.
ROUTE_BUS.mdb
file as the data source from the QlikSenseData
folder.HongKong Buses
.Data load editor.
Create New Connection
and select ODBC.
HongKong Buses
under System DSN.
Qlik Sense CookBook ODBC
.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:
Data load editor
in Qlik Sense.Create new connection
and select OLE DB
.Microsoft Jet 4.0 OLE DB Provider (32-bit)
driver from the provider
drop-down list.Data Source
file path, which, in our case, will be the path for theROUTE_BUS.mdb
file in the QlikSenseData
folder.QlikSense CookBook OLE DB
.Create new connection (OLE DB)
window:If you are extracting the data from a data file, such as .CSV
, perform the following steps:
Data load editor
in Qlik Sense.Create new connection
and select Folder
.QlikSenseData
folder, which contains our data files. Alternatively, you can enter the path of the source folder directly under Path
.Qlik Sense CookBook Data
.Create new connection (folder)
window:Data connections
in the Data load editor
, as shown in the
following screenshot:If you are working with an ODBC or an OLE DB data connection, follow these steps:
Insert connection string
, as shown in the following screenshot:Data connections
to view and extract data from the ROUTE
table in the MS Access database, as shown: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.Keep the Close when successfully finished
option checked in the data load progress window. If the data is loaded successfully, the window automatically closes or else the error encountered is highlighted.
On a similar note, in order to test the Qlik Sense data files:
Select data
option under the Qlik Sense CookBook Data
connection.ROUTE_GMB.csv
file from the QlikSenseData
folder and load it in the application.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
:
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.
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 coordinates 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.
When extracting the data from a web file:
Data load editor
.Create new connection
and select Web
file
.Select web file
window will open.QlikSense Cookbook Webfile
, as shown in the following screenshot:Data connections
, select QlikSense Cookbook Webfile
and click on Select data. This will open up a preview window listing 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
and ensure that it is selected, so that the correct table is shown in the preview. The user will need to change the value under Field names
to Embedded field names
.Insert script
to load the web data in the application.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);
Data model viewer
will show the loaded table.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.
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.
In the last recipe, you learned how to fetch data from a website using the web file connection. With the same connection type, we can extract data from files available in an FTP server.
To demonstrate this recipe, I'm using a local FTP server with user authentication. My server is localhost
, my userid
is qlik
, and my password
is pwd
.
As a general rule, you must use the following parameters in the URL if the server requires authentication to retrieve data from a file ftp://<userid>:<password>@<servername>/path/filename.qvd
.ftp://%3Cuserid%3E:%3Cpassword%3E@%3Cservername%3E/path/filename.qvd
When extracting the data from the FTP server:
Copy Link Loc
ation
:In my example, I chose ROUTE_BUS.csv
:
Data load editor
.Create new connection
and select Web
file
.Select web file
window will open.ftp://qlik:pwd@localhost/chapter%201/QlikSenseData/ROUTE_BUS.csv
.QlikSense Cookbook FTP ROUTE_BUS
, as shown in the following screenshot:
Data connections
, select QlikSense Cookbook
FTP ROUTE_BUS
and click on Select data. This will open up a preview window listing the contents of the file.Field names
to Embedded field names
.Insert script
to load the web data in the application.ROUTE_BUS
and the script will read as follows:ROUTE_BUS: LOAD Route_id, CHANGE FROM [lib://QlikSense Cookbook FTP ROUTE_BUS] (txt, codepage is 28592, embedded labels, delimiter is ',', msq);
Data model viewer
will show the loaded table.Qlik Sense connects to the FTP source file using the stored data connection. Once connected, it identifies the content of the source folders and lists them in the preview window.
If the file is large, the preview will take a long time to open.
For each file, you must create an FTP connection, so this recipe is used in very specific use cases.
If you need to read several files, files that are large in size or both, I recommend using an FTP client to download the files beforehand to a local folder.
You can perform the FTP download using the Windows FTP command. The LOAD
script can trigger the FTP command using EXECUTE
if legacy mode is enabled.
Qlik Sense provides connectivity to several data sources, such as SQL databases, Excel files, and text files, but we also have an increasing need to connect to web services that provide data in the JSON or XML format. This recipe shows you how to configure a REST connection to retrieve data in the JSON format from a public web service with data related to the Star Trek Series.
For this recipe, we will use a URL to extract all the Star Trek movies. When extracting the data from a REST data source:
Data load editor
.Create new connection
and select Qlik REST
Connector.REST
Connector configuration window will open.Authentication Schema
as Anonymous
.QlikSense Cookbook REST
, as shown:Test Connection
to check whether the parameters are OK.Create
to create the connection and close the configuration panel.Data connections
, select QlikSense Cookbook REST
and click on Select data. This will open a preview window listing the tables found in the JSON response from the web service. When you carefully examine the table contents, you realize that it is a root node, and when you expand the node, you find a movies node.movies
and ensure that it is selected, so that the correct table is shown in the preview (when working with JSON data sources, we always have to expand the nodes to find the right table to be extracted).Data model viewer
will show the loaded tables.Insert script
to load the JSON data in the application.RestConnectorMasterTable
. This table contains the JSON data in a flattened format.mainDirector
main table and movies
. In the end, the RestConnectorMasterTable
main table is dropped from memory.Qlik Sense connects to the REST service using the stored data connection. Once connected,
it identifies the source formats and lists them in the preview window.
JSON data sources contains nested data with several children records. Qlik Sense creates a multi-table schema when the source contains nested data.
Qlik Sense REST Connector can also extract data from other data formats, such as XML and CSV. The underlying principles remain the same as explained in the preceding recipes. You can also extract data using authentication methods, the POST method for SOAP web services, or the pagination method to retrieve a large amount of data.
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 running Windows command-line statements or external programs, such as the 7zip file compressor. 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, you must be aware that enabling the legacy mode has security implications, if the application is deployed on the Sense server. You do 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 user has access to the entire filesystem on Qlik Sense Server because the account running the Qlik Sense Engine Service has administrative privileges. Activating the legacy mode requires changing a parameter value in the settings.ini
file for Qlik Sense Desktop. In the Qlik Sense Server, you must have an Administration Role to access the QMC.
The recipe is only valid for Qlik Sense Desktop:
settings.ini
file.settings.ini
file that is, by default, stored under C:\Users\{user}\Documents\Qlik\Sense\Settings.ini
, as shown in the following screenshot:StandardReload=1
to StandardReload=0
.Changing the value for the StandardReload
parameter in the settings.ini
file enables the legacy mode in Qlik Sense. When running in 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.
Qlik Sense has the ability to use the same script that is found in any QlikView file. You can also use a binary load statement in Qlik Sense in order to load the entire data model from an existing QlikView file. We can also run Windows command-line statements.
As any experienced Qlik developer will tell you, the Data model viewer
is a key component you will undoubtedly use 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
:
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.
Data model viewer.qvf
application that has been downloaded from the resource library.Data model viewer
in the Navigation drop-down on the toolbar.In this section, we will see how the different types of data are viewed.
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 details of the available keys (from right to left) are given as follows:
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:
The Data model viewer
also provides information on the contents of each table.
Click the header of the customer address table, and then open the Preview
pane by clicking the Preview
button in the bottom-left corner.
The following preview will be displayed at the bottom of the screen:
Along with a small snippet of the table's contents, the far-left table also provides some high-level table information about the number of rows, fields, keys, and 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:
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 subset ratio of the key and so on.
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 with 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 time-saving functions in Qlik Sense. The effective use of the library saves time spent on scrolling downfield lists, rewriting expressions over and over, and applying a single change in multiple places.
Once you have saved enough time to eclipse the setup investment, the value of taking this approach can only compound with continuous development.
Master Library
.Data load editor
.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 ];
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 at 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 the Data model viewer
:
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.Preview
window will appear, as shown in the following screenshot:Region
field from the table to get the preview, as shown in the following screenshot:Add as dimension
button.Geo
tag and click on Add dimension
button to create a master dimension in the library.Country
and City
fields.Done
to go back to the Data model viewer
.Sales
field from the Data
table in the Data model viewer
.Add as measure
button. When we create a Master measure, we need to make sure we use an aggregation function, such as Sum
or Avg
, along with the selected field.Create new measure
window, type SUM
in front of (Sales
), as shown in the following screenshot:Create
.App overview
.Master items
menu.While the Master items
menu panel is very useful to speed up the development when defining the content, it is easier to do it from the filters pane. In short, you can browse the entire content of your data model and right-click on the most important fields to add the ones that will be frequently used.
Create Dimension
, enter a Description
and any relevant Tags
, and click Done
once finished:As mentioned in the Creating a master library from the Data model viewer 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 (Charts
, Custom objects
, Master items
, and Fields
). 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.
You can continue to use the application from the Creating a Master Library from the Data model viewer recipe:
Data load editor
:LOAD * INLINE [ Country, Area, Quantity USA, North, 1000 USA, North, 1200 USA, South, 2500 USA, South, 2500 UK, North, 1000 UK, North, 2500 UK, South, 2000 UK, South, 1900 ];
Country
and Area
as Master dimensions, both with the Geo
tag.Quantity
as a Master measure.App overview
screen by clicking on the navigation dropdown on the toolbar at the top.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.Area
field to where it says Add dimension
. Repeat the steps where the Country
field selects Add "Country"
when prompted, as shown in the following screenshot:Geo
by pressing the button.Measures
.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: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 delete a field from the data model; the reference will remain part of the Master item pane until it's updated from the edit screen.
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.
If you are a QlikView developer beginning to use Qlik Sense, you are very familiar with the load-script editor and the powerful commands available there, but most business users are not familiar with coding, so Qlik Sense provides Data manager
, a visual data preparation tool tailored for non-technical users. It's a great tool even for experienced QlikView developers.
With data manager, you can create table associations using drag and drop, concatenate tables, and synchronize scripted tables within the data manager.
You can also create table fields that calculate values, create calendar fields, use the data profiling cards to create numeric buckets, and handle string fields with the Replace
, Set nulls
, Order
, and Split
functions.
The data manager can detect geographical data in your table if it have columns with country and city names. It can detect geopoint data (latitude, longitude) for a single location, such as a city or a customer site. It can also detect area data (polygons) to represent regions or countries when loading this information from flat files like TXT or XLS.
Our recipe focuses on some of these functionalities, such as visual data association, concatenation, calculated field, and string replace.
The dataset required for this recipe that is downloaded from the Packt Publishing website (https://www.packtpub.com/big-data-and-business-intelligence/qlik-sense-cookbook-second-edition) comes in a zipped folder called QlikSenseData
. Extract all the files from this zipped folder and save them on your hard drive at the desired location.
The files used in this recipe are Sales Data.xlsx
and Sales Data 2017.xlsx
.
Qlik Sense Cookbook Visual Data Peparation
.Data manager
and the load script editor. Choose Add data from files and other sources
:My computer
in the file location section on the left, and navigate to the folder with the Sales Data.xlsx
file and click in the file:
Add data
:We also have a fourth table with data from 2017, so we need to import that too.
My computer
and select Sales Data 2017.xlsx
. It only has one sheet with data related to 2017.Orders
sheet and click on Add Data
.When we added the data, Qlik sense created a bubble for each table. After inserting a new table with data from 2017, it automatically created a table concatenation between the Orders
table from Sales Data.xlsx
and Sales Data 2017.xlsx
. Note the multi-circle at the top of the table name; it's an indicator for a table created by file concatenation:
To associate the tables and create connections between them, follow these steps:
Customers
table.Customer
table over the Orders
table to create the association between them.
Now we must review the data in the Sales
table. Perform the following steps:
Tables
button at the top-right of the screen to open the table editor. You will see the following screen:Ship Mode
column header to select the field. A profiler window at the bottom shows the distribution of values for each category of data, and the data profiling card on the left. It looks like the following screenshot:Standard Class
and Std Class
(note that the replacement value is the first value selected).Replace
. The card will be updated and show only the replaced value merging Std Class
with Standard Class
.First Class
and 1st Clas
s
as First Class
.Second Class
, 2nd Class
, and Second Clas
as Second Class
.Add a calculated field:
Add field
and select Calculated field
. It will show the calculated field panel editor on the right of the screen.Name
field, write Discount Value
.Expression
box, write Sales * Discount
. The panel provides a preview box showing the result of the expression for each line of the table.Create
. Now you see a new column at the end of the table with the calculated field. An example is shown in the following screenshot:Load data
to reload data in the application.When we create a table association, Qlik Sense profiles your data help you create associations between tables, irrespective of the name of the column on each table. When adding several tables with the same name and column, the data manager creates an automatic concatenation. In all fields of the table, it's possible to transform the data: if the data is a numeric value, you can create buckets. If the column contains string data, you can perform Replace
, Set Nulls
, Order
, or Split
operations. You can create calculated columns as well, but the calculation can only reference columns in the same table.
When doing all this data preparation, the data doesn't load in the memory; you need to click on Load data (the green button) to reload the app with all changes into memory and to start creating visualizations with new tables and fields.
If you are aware of the data you are loading, you can disable data profiling to speed up the load processing in the data manager, especially if you have memory constraints and huge datasets with millions of rows.
When disabling data profiling, Qlik Sense can't recognize location data using city and country names, so you will not be able to visualize data with maps in your app.
The editor also can handle tables with different columns, and you can force the concatenation during column pairing. To do this, click on More options (the button with three dots) at the bottom of the screen in the associations or table editor, and select Concatenate Tables
.
Where there is an eBook version of a title available, you can buy it from the book details for that title. Add either the standalone eBook or the eBook and print book bundle to your shopping cart. Your eBook will show in your cart as a product on its own. After completing checkout and payment in the normal way, you will receive your receipt on the screen containing a link to a personalised PDF download file. This link will remain active for 30 days. You can download backup copies of the file by logging in to your account at any time.
If you already have Adobe reader installed, then clicking on the link will download and open the PDF file directly. If you don't, then save the PDF file on your machine and download the Reader to view it.
Please Note: Packt eBooks are non-returnable and non-refundable.
Packt eBook and Licensing When you buy an eBook from Packt Publishing, completing your purchase means you accept the terms of our licence agreement. Please read the full text of the agreement. In it we have tried to balance the need for the ebook to be usable for you the reader with our needs to protect the rights of us as Publishers and of our authors. In summary, the agreement says:
If you want to purchase a video course, eBook or Bundle (Print+eBook) please follow below steps:
Our eBooks are currently available in a variety of formats such as PDF and ePubs. In the future, this may well change with trends and development in technology, but please note that our PDFs are not Adobe eBook Reader format, which has greater restrictions on security.
You will need to use Adobe Reader v9 or later in order to read Packt's PDF eBooks.
Packt eBooks are a complete electronic version of the print edition, available in PDF and ePub formats. Every piece of content down to the page numbering is the same. Because we save the costs of printing and shipping the book to you, we are able to offer eBooks at a lower cost than print editions.
When you have purchased an eBook, simply login to your account and click on the link in Your Download Area. We recommend you saving the file to your hard drive before opening it.
For optimal viewing of our eBooks, we recommend you download and install the free Adobe Reader version 9.