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).
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.
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:
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?
Qlik View Related Interview Questions
|Microstrategy Interview Questions||IBM Cognos Interview Questions|
|PL/SQL Interview Questions||MSBI Interview Questions|
|VBA For Excel Interview Questions||SAP BO Interview Questions|
|SQL Database Interview Questions||Qlik View Interview Questions|
|R Programming language Interview Questions||Pentaho Interview Questions|
|Advanced SAS Interview Questions|
Qlik View Tutorial
Seeing Is Believing
Data Modeling Best Practices
Basic Data Transformation
Set Analysis And Point In Time Reporting
Advanced Data Transformation
More On Visual Design And User Experience
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.