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:

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.

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?