Qlik Sense Cookbook - Second Edition

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):

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

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

If you are working with an ODBC or an OLE DB data connection, follow these steps:

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:

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.

This recipe aimed to extract data from common data sources, such as RDBMSes and data files. Qlik Sense can also extract data from web files and web services. We will see this in the next section.

When extracting the data from a web file:

Country_Location: 
LOAD 
Country, 
Capital, 
Latitude, 
Longitude 
FROM [lib://QlikSense Cookbook Webfile] 
(html, codepage is 1252, embedded labels, table is @2); 

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.

When extracting the data from the FTP server:

In my example, I chose ROUTE_BUS.csv:

ROUTE_BUS: 
LOAD 
    Route_id, 
    CHANGE 
FROM [lib://QlikSense Cookbook FTP ROUTE_BUS] 
(txt, codepage is 28592, embedded labels, delimiter is ',', msq); 

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 can also extract data from other data formats, such as XML, XLSX, XLS, or QVDS. The underlying principles remain the same as explained in the preceding recipes.

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.

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.

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.

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 details of the available keys (from right to left) are given as follows:

• Collapse all: This reduces 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
• Show 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 metadata

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:

• 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 subset ratio of the key and so on.

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

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:

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.

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

You can continue to use the application from the Creating a Master Library from the Data model viewer recipe:

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
 ]; 

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.

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.

We also have a fourth table with data from 2017, so we need to import that too.

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:

Now we must review the data in the Sales table. Perform the following steps:

Add a calculated field:

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.