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 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 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.
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.
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:
Initial Load
as the name of the new tab and click on OK.T_F41SCHEDULE_P1A_EMP.qvd)
stored in the 3.QVDSource
folder.Temp_Employment:
before the Load
keyword of the generated script. Remove the Directory;
statement if necessary.Transformation
as the name of the tab and click on OK.Temp_Employment
table via a Resident
load. We will also name this new table as Employment
. Write the following code:Employment: Load [%Airline ID], Year, [Month (#)], [# Total Employees], Period, Month, [%Unique Carrier Code], [Unique Carrier], [Carrier Code], [Carrier Name], [%Carrier Group ID], [# Full Time Employees], [# Part Time Employees], [# Equivalent FTEs] Resident Temp_Employment;
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, 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 NoConcatenate
keyword.
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:
Employment: Load [%Airline ID], Year, [Month (#)], [# Total Employees], Period, Month, [%Unique Carrier Code], [Unique Carrier], [Carrier Code], [Carrier Name], [%Carrier Group ID], [# Full Time Employees], [# Part Time Employees], [# Equivalent FTEs] Resident Temp_Employment Order By [%Airline ID], Year, [Month (#)];
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.
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:
Employment: Load If( [%Airline ID] = Peek('%Airline ID', -1), [# Total Employees] - Peek('# Total Employees', -1), 0 ) as [# Delta Total Employees], [%Airline ID], Year, [Month (#)], [# Total Employees], Period, Month, [%Unique Carrier Code], [Unique Carrier], [Carrier Code], [Carrier Name], [%Carrier Group ID], [# Full Time Employees], [# Part Time Employees], [# Equivalent FTEs] Resident Temp_Employment Order By [%Airline ID], Year, [Month (#)];
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:
Employment: Load If( [%Airline ID] = Peek('%Airline ID', -1), [# Total Employees] - Peek('# Total Employees', -1), 0 ) as [# Delta Total Employees], If( [%Airline ID] = Peek('%Airline ID', -1), [# Full Time Employees] - Peek('# Full Time Employees', -1), 0 ) as [# Delta Full Time Employees], If( [%Airline ID] = Peek('%Airline ID', -1), [# Part Time Employees] - Peek('# Part Time Employees', -1), 0 ) as [# Delta Part Time Employees], If( [%Airline ID] = Peek('%Airline ID', -1), [# Equivalent FTEs] - Peek('# Equivalent FTEs', -1), 0 ) as [# Delta Equivalent FTEs], [%Airline ID], Year, [Month (#)], [# Total Employees], Period, Month, [%Unique Carrier Code], [Unique Carrier], [Carrier Code], [Carrier Name], [%Carrier Group ID], [# Full Time Employees], [# Part Time Employees], [# Equivalent FTEs] Resident Temp_Employment Order By [%Airline ID], Year, [Month (#)]; Drop Table Temp_Employment; Store Employment into [..3.QVDsTransformedEmployment Statistics.QVD]
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 dataset), 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.