Merging forces - Qlik View

On their own, the Order By statement and the Peek function are already powerful. Now, imagine what happens when we combine both of these tools to enhance our input data. In this section, we will use both of these functions to add a new calculated field to our Employment table (the one we integrated to our data model in Chapter Data Modeling Best Practices).

A refresher

The Employment table provides information regarding the monthly number of employees per airline. The total number is split between part and full time employees, and it also shows the total FTEs (Full Time Equivalent).

The objective

The executives of HighCloud Airlines have asked the QlikView team to create a report that shows the monthly change in number of employees in a line chart to discover and analyze peaks in the employment behavior of each airline.

Getting it done

First, how do we find the total change in number of employees for this month compared to the last? Well, we take the number of employees in the current month and subtract the number of employees we had in the previous month. If the number is zero, it means there was no change (no one fired!), if the number is greater than zero, it means we have new hires in the house; last, and hopefully the least, if the number is less than zero, it means we will be missing some colleagues.

To add this field to our Employment Statistics table, and following the best practices we previously discussed, we will create a new QlikView document, used for transformations, and save it inside the 2.Workbooks folder. Name this file as Transform-Employment Data.qvw. The resulting table will then be saved as QVD inside the 3.QVDTransformed folder.

Loading the table

Once you have the new QlikView document created, saved and still open, go to the Edit Script window (Ctrl + E) and perform the following steps:

  1. Add a new tab to the script by clicking on the Tab menu and selecting Add Tab..
  2. From the Tab Rename Dialog window, type Initial Load as the name of the new tab and click on OK.
  3. Use the File Wizard dialog to load the Employment Statistics table from the corresponding QVD file (T_F4iscHEDULE_PiA_EMP.qvd) stored in the 3.QVDsource folder.
  4. Click on Finish on the first dialog window of the File Wizard dialog since no alterations will be made to the file on the initial load.
  5. Assign the table a name by typing Temp_Employment: before the Load keyword of the generated script. Remove the Directory; statement if necessary.
  6. Now, add a new tab to the right of the Initial Load tab, by clicking on Add Tab. from the Tab menu.
  7. In the Tab Rename Dialog window, type Transformation as the name of the tab and click on OK.
  8. Once in the Transformation tab, we will create the script to load the previously created Temp_Employment table via a Resident load. We will also name this new table as Employment. Write the following code:

We are now ready to add the transformation functions to the table. It's important to note that, if we reload the script at this point, the new employment data will never be created because of the Natural Concatenation feature we talked about in Chapter Data Modeling Best Practices, since both the Temp_Employment table and the Employment table will have exactly the same number of fields as well as the same field names. However, with the functions we will apply, and the new fields we will add, this structural similarity will be lost and we will not need to add the No Concatenate keyword.

Sorting the table

Using the techniques learned in the Sorting tables section of this chapter, we will set the load order of the Resident table using the Airline ID, Year, and Month # fields. The earlier script will be modified to:

Take note of the order in which the sorting fields are defined. The ordering output is: all records will be first sorted by Airline id, for each airline, the records will then be sorted by Year in ascending order, and then, for each year, the records will be sorted by Month from first to last. In our case, the Airline id sorting can be either ascending or descending, it doesn't matter. However, Year and Month # must be sorted in ascending order, which is the default if no sort order is specified.

Peeking previous records

The final step will be to take the sorted table and start comparing adjacent months to find out the difference in number of employees between them. We've seen how the Peek function will bring a value from previous records, but in our case it gets a little trickier, since we need to be careful not to peek into and compare records corresponding to different airlines. An If expression should be used in conjunction with the Peek function. The adjustment we will make to the previous script will result in:

We are almost ready to reload our script and see the result. We just need to add a Drop statement to remove the Temp_Employment table from RAM after using it in the Resident load script. Add the following code at the end of the Transformation tab:

Drop Table Temp_Employment;

After this, save the changes we've made to the QlikView document and hit Reload (or press Ctrl+R). The script will perform the transformation and, after it's finished, we can open the Table Viewer window and preview the resulting Employment table. Here is what we'll see:


From the Preview dialog window, we can see how the very first airline (19386) has had an erratic behavior in their headcount. In February 2009, they had a bump of 54 employees, and in the following month their headcount dropped by the same amount. Then, a massive reduction of 3568 took place in May 2009.

Now that we've added the # Delta Total Employees field, let's add the corresponding delta fields for part-time and full-time employees, as well as FTEs. We will also add the store command to save the output table to a QVD file.

Our modified script will be:

Adding these fields to our table makes it easier to perform more in-depth analyses, such as the ones shown in the following screenshot:


From the previous charts we can see that, while most carriers experienced a downsize in headcount from April 2009 to April 2010 (the selected data set), Delta Air Lines Inc. grew its staff by about 32,000 employees in the same period.

By integrating this data into the final data model, may be able to find correlations between hires, downsizings, # of flights, enplaned passengers, flight occupancy, and so on. This enables the QlikView users at HighCloud Airlines to better make business decisions.

A solo exercise

By now, you are well armed, so what about a little challenge?

We've already added the fields for Monthly Headcount Change. How would we go about adding new fields for Quarterly Headcount Change and Annual Headcount Change? What information can you get from the resulting data?

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Qlik View Topics