The heart of a QlikView application is its data model. It is composed of the different source tables that contain the information and data used to measure a company's performance. The data model is constructed by using QlikView's scripting language.

A correctly-built data model will associate all of its tables in a way which allows us to manipulate the data however we like. This means that the creation of analysis objects (charts) across different dimensions depends mainly on how the data model is built and how its tables are associated (how they are linked to each other).

To start building our data model, we will load the fact table of our source data files into QlikView.

A fact table is a table that contains the measurements across which we'll make the analyses. The fact table is, at the same time, the central part of the data model.

Follow these steps to load a fact table:

Our data model now contains the Main Data table.

The first object we will look at in this tutorial is the listbox. A listbox is the most basic of all QlikView objects and contains all the occurring values for a given field in the data model. As demonstrated in Chapter 1, Meet QlikView, a listbox is used to make selections in the document and filter the data.

To start using this object and better understand its function, bring up the Sheet Properties dialog window by right-clicking in a blank space inside the sheet area, then selecting Properties… from the context menu. Once the Sheet Properties window is open, make sure the Fields tab is active.

From the Available Fields list on the left, add the Carrier Name, Origin City, Origin Country, Origin State, Destination City, Destination Country, and Destination State fields to the Fields Displayed in Listboxes list on the right by highlighting each of them and clicking on the Add > button.

The following screenshot shows the Sheet Properties dialog window:

Click on OK to apply the changes.

The procedure we followed above will add one listbox for each of the fields we selected. Let's take a moment to position them in our workspace.

The listboxes will initially all be placed on top of each other. Go to the Layout menu and click on Rearrange Sheet Objects and they will be dispersed across the screen space.

Sometimes it's necessary to click on this command more than once because the objects don't get properly distributed at first. So, if necessary, click on Rearrange Sheet Objects two or three times until all of the seven listboxes we added are properly spread throughout the screen space.

You can also click and drag individual objects to position them in the place you want them on the screen. Make sure to click on the caption bar of the object to be able to drag it.

In the previous chapter, we talked about how listboxes work, so you should now be familiar with it and the color-coding used to mark selected, associated, and excluded values respectively. Even so, let's use some of the listboxes added above and reinforce these concepts.

Let's click on the Adana, Turkey value in the Origin City listbox. This action will filter the dataset to show only information regarding flights departing from Adana, Turkey. We can instantly see how the selected value will turn green. The rest of the listboxes will also be updated to show the data that is associated with the specific value we just selected. The values associated with our selection will have a white background and the data that is excluded (that is, those values that have no relation with our selection) will be shown with a gray background. The new selection state is depicted in the following screenshot:

Which carriers have logged flights departing from Adana, Turkey? In which cities and states are those flights arriving? These questions are easily answered by QlikView's associative engine by simply selecting a value in the Origin City listbox. One click gives us multiple answers.

After a selection is made, QlikView updates the charts and objects in the document to match that particular request. This selection process is similar, in a way, to making filters in an Excel table. It's also similar to making a query in a database, but with the code part laid aside.

Using the associations presented in the previous screenshot, we can confidently make the following affirmations:

Click on the Clear button, shown in the following screenshot, in the Navigation toolbar to reset the selection state.

At this point, the sole table we loaded provides a lot of usable information about airline traffic for us to analyze. However, while there is a lot of data, there are only a few descriptive values. Instead, the table contains references (identifiers) to values stored in other tables. We need to integrate additional tables into the data model which will provide the description to those identifiers and, by doing so, we will enrich the meaning and context of our data and allow for more insight into the analyses we make.

Structuring the script

In order to add more tables to the data model, we need to add the corresponding Load statements to the script. In order to keep things tidy, we will separate some of these Load statements and store them in different tabs in the Edit Script window. That way, we will keep our script well-structured. Since we already have a Load statement (the one we created in the previous section), let's place it in its own tab.

Go to the Edit Script window (Ctrl + E) and position the cursor on the line directly above the name we assigned to the first table ([Main Data]). Then, go to the Tab menu and select Insert Tab at Cursor…. The Tab Rename Dialog window will appear, in which we will type Main Data, to name the new tab, and click on OK. The code we generated previously will be moved to this new tab.

Now, let's load the remaining tables. We'll start by adding the Carrier Groups table, using the following steps:

1. Activate the tab on the far right, which should be the one named Main Data, and select Tab | Add Tab…. In the Tab Rename Dialog window, type Airlines and click on OK.
2. Click on the Table Files… button and browse to the Carrier Groups.qvd file located in the Data Files\QVDs folder. Highlight it and click on Open.
3. The Qvd file type should automatically show as selected in the left pane of the File Wizard: Type window. Click on Finish to close the dialog window.
4. Remove the Directory; instruction and assign a name to the table by typing [Carrier Groups]: right above the Load statement.

Take a moment to follow steps 2 through 4 for the remaining tables, which are listed below, but assign a different table name to each of them in step 4.

Note

Before adding a new Load statement, make sure the cursor has been placed in a new line in the Script Editor.

We will add the tables contained in the following files to the following tabs:

• Airlines tab:
  • Airlines.qvd
  • Carrier Operating Region.qvd
  • Flight Types.qvd
• Aircrafts tab (follow step 1, above, to create it):
  • Aircraft Groups.qvd
  • Aircraft Types.qvd
• Airports tab (follow step 1, above, to create it):
  • Distance Groups.qvd

Note

QlikView script is followed from top to bottom and then left to right across the tabs. As a best practice, each new source table should be placed in its own tab whenever possible.

After adding these tables and reloading the script (as just described), we can press Ctrl + T to bring up the Table Viewer window, which shows the newly constructed data model as in the following image:

Each blue-bordered box represents a loaded table, and it lists the fields contained in that table. We can also see a blue line that shows the connection between any two tables and marks the associations generated by QlikView in the data model.

Tip

To rearrange the layout of the data model in the Table Viewer window, click on the Auto-Layout button or click and drag the table titles.

The rule for two tables to be linked is simply that they must share a field with the same name. As a developer, you can use alias field names to link or unlink tables and ensure the created associations are correct. Table Viewer is very helpful when verifying table associations.

Note

There is another rule for constructing data models: Two tables should be linked by only one field. If they have two or more fields in common, a Synthetic Key will be created which can be a potential issue that needs to be addressed. We'll cover these rules in depth in Chapter 5, Data Modeling.

We will start by adding user controls in the form of listboxes to our new sheet.

Right-click on a blank space of the sheet area and click on Select Fields…. Then, add the following fields to allow filtering: Year, Quarter, Month, Carrier's Operating Region, Carrier Group, Aircraft Group, and Flight Type.

After adding the specified fields, click on OK.

We will adjust some of the properties in the created listboxes, starting with the Year listbox. Right-click on it and select Properties… from the context menu.

The changes we will make to this listbox are set in the Presentation tab of the Properties dialog window. Adjust the following settings:

Follow the earlier procedure for the Quarter, Month and Carrier's Operating Region listboxes, changing only the Fixed Number of Columns setting as follows:

Let's now reposition these listboxes appropriately on the screen space, and resize them if needed. We should have them placed in a way more or less similar to those in the following screenshot:

Optimizing the screen space

As you may have noticed, we still need to include a few more fields for filtering, but don't have much space available (since we must reserve the main part of the screen for charts and tables). We will make use of an additional object type to optimize the screen space used by listboxes: the search object.

The search object will allow the user to search for information related to airlines and carriers, as well as aircrafts. All of this by using only a small space on the screen.

Click on the Create Search Object button, shown below, located on the design toolbar.

From the New Search Object dialog window, we can specify which fields the object search will go through when the user types a search string. We will enable the search across Selected Fields, so make sure the corresponding radio button is selected, highlight the fields to be added, and clicking on the Add> button.

The following fields need to be added: Aircraft Type, Airline, Carrier Code, Carrier Name, Destination City, Destination Country, Destination State, Origin City, Origin Country, Origin State, and Unique Carrier.

Note

The order in which the fields are added is not important.

Click on OK and reposition the search object, shown below, to the upper-left corner of the screen by clicking on the magnifying glass and dragging it with the mouse.

The way the Search object works is outlined below:

1. The user clicks inside the Search object and types a search string. A search string is any word(s) or set of characters the user is interested in finding within the loaded data.
2. All field values containing the specified search string will be listed below the search box. The matching search string will be highlighted in yellow and grouped by the field in which the value is found. For example, the following screenshot shows results for the search string South:
   
3. When the total matching values in any given field exceed a certain number (10 by default), all of the corresponding values will be collapsed. Otherwise, they will all be listed. You can change the default limit value from the Presentation tab of the Properties dialog window.

Now that our search object and listboxes are set up, let's create a few charts.

Our first analysis object will be a bar chart which will show the number of logged flights per year.

Locate the Create Chart button, shown in the following screenshot, in the design toolbar and click on it.

The Create Chart wizard will appear. In the Window Title field, enter Traffic per year. From the Chart Type section, select the Bar Chart option (the first one to the left) and click on Next.

The next dialog window is Dimensions. A dimension is a field across which data is aggregated on the chart.

From the list on the left, locate and highlight the Year field and add it to the Used Dimensions list by clicking on the Add > button. After that, click on Next, as shown in the following screenshot:

We will now deal with the expression, which is the formula QlikView will use to calculate the metric we want. In this case, we want to get the total (a sum aggregation) number of flights performed. In the Edit Expression dialog, which opens automatically after clicking on Next in the previous window, type:

Sum ([# Departures Performed])

The Edit Expression window is shown in the following screenshot:

Click on OK to continue.

We will assign a label to our expression by entering # of Flights in the Label field of the Expressions dialog.

We will continue making a few additional adjustments to our chart in a moment, but for now we'll just click on Finish to exit the Create Chart wizard, which is composed of several tabs. You should see something similar to the following screenshot:

We will now go back to the Properties dialog (right-click on the chart and select Properties…), which contains the same options as the Create Chart wizard, and make the following adjustments to our chart:

After clicking on OK to apply the above changes, resize and reposition the chart on the screen to occupy an appropriate part of the upper space of the window. What we should have so far is:

With the above user interface, a user can now start making queries, filter data, and see how the chart updates with every new selection. We can, for example, ask a question like: How many flights per year have been performed by foreign carriers? How many of those flights were domestic and how many were international?

However, let's enhance the functionality a bit more.

We will now add one more level of interactivity to our chart by using a Cyclic Expression group. Having a cyclic expression means the user will have the ability to interactively change the measure or formula used in a chart.

The cyclic group we will create will hold expressions to calculate the number of flights, number of enplaned passengers, total freight, and total mail transported.

To create a cyclic expression, right-click on the Traffic per year chart and select Properties… from the context menu.

Since we already have the first expression created, we will continue by beginning to create the second one. Activate the Expressions tab and click on the Add button. In the Edit Expression dialog window, type the following expression:

Sum ([# Transported Passengers])

Click on OK and set the label for this expression as # of Enplaned Passengers.

Make sure this second expression is highlighted and click on the Group button. This will automatically create the cyclic group for our expressions.

Click on the Add button once more and add the following expression:

Sum ([# Transported Freight])

The label for this expression will be Transported Freight. Make sure the new expression is highlighted and click on the Group button again.

Click on the Add button again to add our last expression:

Sum ([# Transported Mail])

The label for this expression will be Transported Mail. Make sure the new expression is highlighted and click on the Group button again.

Finally, go to the Number tab and make sure that all of our expressions are formatted as Integer. Click on OK to apply the changes.

After following this procedure, our chart will have the ability to change its active expression (metric) through a cycle button (in the form of a circular arrow) that will be placed at the lower-left corner of the object.

The user can select the measure that he/she wishes to activate by either clicking on it directly to sequentially change the chart's expression, or by clicking on the little black down-arrow to display the drop-down menu from which the desired expression can be selected.

Our chart should look like the following screenshot:

Using the same chart object as in the previous section, we will now make use of a different kind of dimension: Drill-down Group. We will change the Year dimension with a hierarchical group that will contain the Year and Month fields.

First, right-click on the bar chart created above and select Properties…. Activate the Dimensions tab, then locate the Edit Groups… button at the lower-left corner and click on it.

The Groups dialog window will pop up. Click on the New… button and, from the Group Settings dialog window, enter Time as the Group Name, making sure the Drill-down Group radio button is selected.

From the Available Fields list on the left, locate the Year and Month fields, and add them to the Used Fields section on the right by highlighting them and clicking on the Add> button. Make sure the fields are added in the correct order.

The Group Settings dialog window should now look like the following:

Click on OK to apply the changes in the Group Settings dialog, and click on OK again to close the Group window.

You will now see the newly created group in the Available Fields/Groups list in the Dimensions window. Highlight it and add it to the Used Dimensions list by clicking on the Add> button. Then, remove the one we previously had (Year) by highlighting it and clicking on the <Remove button.

Click on OK.

We now have a bar chart showing the number of flights per year. We can drill down to see a monthly trend by clicking on one of the bars, which initially represents a year. Another way of drilling down is by making an in-chart lasso selection covering one of the bars. Our chart should look like the following screenshot:

With the functionality presented in this section, as well as in the previous one, the user is free to decide how he/she wants to visualize the data: selecting, slicing, drilling, and swapping as per his/her convenience. Also, the space taken up by one single chart can be used to make a lot of different analyses with just a few clicks.

We will now add a chart in the form of table to display the top routes in terms of number of flights, enplaned passengers, transported freight, and transported mail. Let's call it Top 10 Routes.

Start by clicking on the Create Chart button from the design toolbar. From the first dialog in the New Chart wizard, select the Straight Table icon, shown below, as Chart Type and set Window Title to Top 10 Routes. Click on Next.

In the Dimensions window, add the From – To City field to the Used Dimensions list and click on Next.

We will add the following four expressions and their corresponding labels:

After adding the expressions, make sure to set Total Mode to No Totals for all four of them. This is done by selecting the corresponding radio button at the lower-right corner of the window.

The Total Mode section is shown in the following screenshot:

Click on Next two times to open to the Presentation dialog window. Once there, enable the Max Number (1 – 100) checkbox and set it to 10.

Click on Next three times to get to the Number dialog window and make sure to set all of the expressions to the Integer format.

Click on Finish and, after rearranging and resizing the objects, we should have the following:

For the table to actually show the top values, we need to sort one of the four expression columns in either ascending or descending order. To do that, double-click on the header cell of the column you want to sort. The first time the column is sorted, it will use the ascending order. You can double-click it again to sort in descending order.

Now that we have prepared a QlikView document, we can explore and discover the data contained in it. We can interact with our document, make selections, and use the charts to make sense of the information.