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
.
- Create a new Qlik Sense application and name it
Qlik Sense Cookbook Visual Data Peparation
. - When starting a new application, Qlik Sense always asks to choose between the
Data manager
and the load script editor. ChooseAdd data from files and other sources
:
- Click on
My computer
in the file location section on the left, and navigate to the folder with theSales Data.xlsx
file and click in the file:
- After selecting the file, we can see a list with all sheets in the Excel file. Select all sheets, as in the following screenshot:
- Click on
Add data
:
We also have a fourth table with data from 2017, so we need to import that too.
- Click on the Plus circle in the top-left corner to insert another table:
- Open
My computer
and selectSales Data 2017.xlsx
. It only has one sheet with data related to 2017. - In the preview windows, select the
Orders
sheet and click onAdd Data
. - Once the connection is created, we can start to model and prepare our data with the associations and table editors:
- Open
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:
- Click on the
Customers
table. - Check whether any other table has a green bar. The green bar shows which table has the stronger match for the association. The more it is filled, the stronger a candidate it is for the association:
- Drag the
Customer
table over theOrders
table to create the association between them. - Click on the association to review which field was used:
Now we must review the data in the Sales
table. Perform the following steps:
- Click on the
Tables
button at the top-right of the screen to open the table editor. You will see the following screen:
- Move the mouse pointer over the Order box and click on the pencil to edit the table:
- In the table editor, click on the
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:
- There are some data mismatches in the column that we need to fix using the replace card. Select
Standard Class
andStd Class
(note that the replacement value is the first value selected). - Click on
Replace
. The card will be updated and show only the replaced value mergingStd Class
withStandard Class
. - Repeat the steps to merge
First Class
and1st Clas
s
asFirst Class
.
- Repeat the steps to merge
Second Class
,2nd Class
, andSecond Clas
asSecond Class
. - We have replaced several values to keep with only four distinct values, as in the following screenshot:
Add a calculated field:
- In the same table, click on
Add field
and selectCalculated field
. It will show the calculated field panel editor on the right of the screen. - In the
Name
field, writeDiscount Value
. - In the
Expression
box, writeSales * Discount
. The panel provides a preview box showing the result of the expression for each line of the table.
- Click on
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:
- Click on
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
.