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
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.
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.
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:
Install the relevant ODBC drivers on your system.
While setting up the ODBC connection, select the
ROUTE_BUS.mdbfile as the Data Source from theQlikSenseDatafolder.Create a new Qlik Sense application and open the data load editor.
Click on the Create New Connection and select ODBC.
Select HongKong Buses under System DSN.
Name the data connection as
Qlik Sense CookBook ODBC.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:
Open the data load editor in Qlik Sense.
Click on the Create New Connection and select OLE DB.
Select the Microsoft Jet 4.0 OLE DB Provider (32 Bit) driver from the provider drop-down list.
Insert the Data Source file path, which in our case will be the path for the
ROUTE_BUS.mdbfile in theQlikSenseDatafolder.Name the data connection as
QlikSense CookBook OLE DB.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:
Open the data load editor in Qlik Sense.
Click on Create New Connection and select Folder.
Select the location of the
QlikSenseDatafolder which contains our data files. Alternatively, one can directly enter the path of the source folder under Path.Name the data connection as
Qlik Sense CookBook Data.The following image shows the details we enter in the Create new connection (folder) window:
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:
If you are working with an ODBC or an OLEDB data connection, follow the steps:
Insert the relevant data connection string to the script by clicking on Insert connection string, as shown in the following screenshot:
Next, click on Select data under Data connections to view and extract data from the
ROUTEtable in the MS Access database, as shown:
The preview of the
ROUTE_BUS.mdbtable 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:
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.- 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.
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.
Next, select the
ROUTE_GMB.csvfile from theQlikSenseDatafolder and load it in the application.The preview of the
ROUTE_GMB.csvtable 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.
Insert the script and then save and load it.
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 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.
When extracting the data from a web file:
Open an existing Qlik Sense application or create a new one.
Open the data load editor.
Click on Create New Connection and select Web file.
The Select web file window will open.
Insert the following URL from which you can fetch the data:
http://www.csgnetwork.com/llinfotable.html
Name the connection as
QlikSense Cookbook Webfile, as shown:
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.
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.
The preview of the table will look like the following screenshot:
Select all the fields from the table in the preview window. Click on Insert script to load the web data in the application.
Name the table as
Country_Locationand 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);
Save and load the data. Once the script is successfully loaded, the 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.
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.
Activating the Legacy Mode requires changing a parameter value in the settings.ini file for Qlik Sense.
Make sure that Qlik Sense Desktop is closed before opening the
settings.inifile.Open the
settings.inifile that is by default stored underC:\Users\{user}\Documents\Qlik\Sense\Settings.ini.Change
StandardReload=1toStandardReload=0.Save the file and start Qlik Sense Desktop in order to run it in a legacy mode, as shown:
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.
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.
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.
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.
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:
Activate the legacy mode as described in the recipe just prior to this.
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;"";
Open Qlik Sense. Copy and paste the SAP Connection string from the script editor of the QlikView file to Qlik Sense.
Similarly, one can copy and paste the load script generated for any SAP table in a QlikView file to a Qlik Sense file.
Save and load data.
The data load editor with all the connection strings will appear, as shown in the following:
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.
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.
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.
For this recipe, we will use the Automotive.qvf file, which comes as a built in example when we install the Qlik Sense Desktop.
Open the
Automotive.qvffile from the Qlik Sense desktop hub.- Click the Help (
) button inside the data load editor. This will highlight the script so that all the keywords are then clickable links.
Click on the keyword
pickin the script. This will take us to the correct place in the help file, as shown:
An alternative approach that can be used in Qlik Sense versions prior to 2.0.1 is as follows:
Highlight the key word
pickin the script.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.
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:
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.
Open the
Data model viewer.qvfapplication that has been downloaded from the resource library.Click on data model viewer in the Navigation dropdown 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 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
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 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.
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.
Create a new Qlik Sense application and name it
Master Library.Open the data load editor.
- 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 ];
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.
In the data model viewer, select the
Datatable by clicking on its header and then click the Preview button to view the fields and the field values loaded from theDatatable.The Preview window will appear as shown in the following:
Select the Region field from the table to get the preview as shown in the following:
- Next, click the
button. The following window appears. If you are likely to publish this dimension for consumption by users, you can enter a description here:
-
It is advised to use tags to make our life easier. Add the tag Geo and click on
. - Now click on
to create a Master dimension in the library. Repeat this process for the Country and City fields.
- Click on
to go back to the data model viewer. Finally, it's time to create a measure. Select the Sales field from the
Datatable in the data model viewer.- 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. In the Create new measure window, type
SUMin front of (Sales), as shown in the following image:
Click on Create.
-
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. - Open (or create) a sheet and enter the edit mode by clicking on the
button. - 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. 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.
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.
You can continue to use the application from the previous recipe.
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 ];
Add Country and Area as Master dimensions both with the tag Geo.
Add Sales as a Master measure.
Open the App overview screen by clicking on the navigation dropdown on the toolbar at the top.
- Enter the edit mode by clicking on the button.
- 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. - Type
Geoin 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
. 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:
- Clear your search on Geo by pressing the
button. 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:
You can now drag this chart into the asset panel and it will become a master visualization.
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.
