Using visual data preparation on the data manager model viewer

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.

Getting ready

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.

How to do it...

1. Create a new Qlik Sense application and name it Qlik Sense Cookbook Visual Data Peparation.
2. When starting a new application, Qlik Sense always asks to choose between the Data manager and the load script editor. Choose Add data from files and other sources:

3. Click on My computer in the file location section on the left, and navigate to the folder with the Sales Data.xlsxfile and click in the file:

4. After selecting the file, we can see a list with all sheets in the Excel file. Select all sheets, as in the following screenshot:

5. Before proceeding to the next step, check whether the data profiling is active. Click on the button with the three dots to check:

6. Click on Add data:

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

7. Click on the Plus circle in the top-left corner to insert another table:
   • Open My computer and select Sales Data 2017.xlsx. It only has one sheet with data related to 2017.
   • In the preview windows, select the Orders sheet and click on Add Data.
   • Once the connection is created, we can start to model and prepare our data with the associations and table editors:

How it works...

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:

1. Click on the Customers table.
2. 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:

3. Drag the Customer table over the Orders table to create the association between them.
4. Click on the association to review which field was used:

5. The field is Customer ID.
6. Repeat steps 1 through 5 for the Products and Salesman tables.
7. At the end, the tables' connections will look like the following screenshot:

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

1. Click on the Tables button at the top-right of the screen to open the table editor. You will see the following screen:

2. Move the mouse pointer over the Order box and click on the pencil to edit the table:

3. 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:

4. There are some data mismatches in the column that we need to fix using the replace card. Select Standard Class and Std Class (note that the replacement value is the first value selected).
5. Click on Replace. The card will be updated and show only the replaced value merging Std Class with Standard Class.
6. Repeat the steps to merge First Class and 1st Class as First Class.

7. Repeat the steps to merge Second Class, 2nd Class, and Second Clas as Second Class.
8. We have replaced several values to keep with only four distinct values, as in the following screenshot:

Add a calculated field:

1. In the same table, click on Add field and select Calculated field. It will show the calculated field panel editor on the right of the screen.
2. In the Name field, write Discount Value.
3. In the Expression box, write Sales * Discount. The panel provides a preview box showing the result of the expression for each line of the table.

4. 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:

5. 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.

There's more...

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.