There are several conditional functions in QlikView that can be used in the frontend to give our charts a higher level of flexibility in terms of handling and presenting the data, both in expressions and dimensions. Let's go through some examples of these functions to enhance the analysis in our QlikView app.
The If function
Though sometimes neglected because of its high resource usage when compared to other methods (such as Set Analysis, which is covered in the next chapter), the If function has important uses when creating QlikView documents. Essentially, it is used when two or more different outputs should result from a single expression, depending on a condition that is evaluated to either be true or false.
In this section, we'll explain how it works and discuss a use case.
As the first step, we should describe the syntax used by this function and the parameters it needs to work. If you've worked with Microsoft Excel previously, then it's very likely you well have come across the If function in a spreadsheet.The syntax of the If function in QlikView is almost the same as that in Excel.The function takes the following three parameters:
A use case
You should be advised upfront that the If function is, in fact, resource heavy. It's often a good alternative to move all calculations based on the If function from the frontend to the script whenever possible and handle the results through calculated fields or flags in the data model. However, there are cases in which the calculation cannot be handled anywhere but in the frontend. Here, we describe one scenario in which you can take advantage of the If function's capabilities.
A heat chart is a cell matrix in which each individual value is color-coded based on a threshold. We will create one of those charts using the Airline Operations document to demonstrate the concept.
Click on OK to close the Edit Expression dialog.
Notice how we are not recalculating the Load Factor % value but instead referencing the actual expression we created in the previous step, using the label we set. This will help us save some valuable CPU resources.
Initially, only the first dimension (Carrier Name) will be visible. Expand the other two dimensions and drag them to the top to create the matrix. The heat map should look like the following screenshot:
To accommodate the new chart into the Analysis sheet, enable the Auto minimize option in the Passengers vs Mail scatter chart. Then, resize and position the new heat chart to occupy the same space as the scatter chart.
Resize and position their corresponding minimized icons as well, just as we did with the Reports tab previously.
A solo exercise
It's time for a little challenge. We've defined the threshold with static limits (0.85 and 0.7). How can we make them variable, and how can we let the users define their own limits?
The key is in the first section of this chapter.
Building a heat chart with the Colormix wizard
In our previous example, we used the Green(), LightBlue(), and LightRed() color functions in conjunction with the If function to define the Background color attribute. Now, we will edit the attribute's expression to make use of the Colormix Wizard option and see if we can come up with a better looking heat chart.
The modified heat map should now adopt the following look:
If we compare the new chart to what we had previously, it's evident that the new look is more dynamic and has additional levels of intensity, thus providing a more detailed insight.
Numeric versus text comparisons
An important consideration when inserting comparisons into conditional functions is that text-based comparisons will be slower than numeric comparisons.
Take the following two expressions, for example:
The only difference is that the first expression is comparing the Month value as a literal value, that is, a text, while the second expression is performing the comparison based on a numeric value. The latter will be faster.
Similarly, it's also important to consider that, when defining a numeric comparison value, it shouldn't be enclosed in single quotes (MonthNum = 'l') as it will cause QlikView to treat it as a text-based comparison.
The Class function
Conditional functions are widely used in conjunction with numeric intervals either to find where a specific value falls in a set of ranges, or to group results into predefined bins. The Class function is particularly helpful in these cases.
Take, for example, the analysis we made in a previous section about the number of interstate routes per carrier. The initial chart is:
We can group the carriers based on the number of interstate routes they serve, only instead of using the individual number of routes, we can use intervals. Add the following expression to our chart:Class(Count(Distinct [From - To State Code]), 100)
The result will be the following screenshot:
Essentially, the Class function takes the individual result of an expression, in this case the count of routes, and automatically creates the corresponding bin based on the bin width specified.
The Class function only supports fixed bin widths.
We can take this further to use the Class function into a nested aggregation, by having a new straight table chart with the following calculated dimension:Aggr(Class(Count(DISTINCT [From - To State Code]), 100), [Carrier Name])
Add the following expression:Count(DISTINCT [Carrier Name])
The result will be as seen in the following screenshot:
As you can see, this one is easier to read than having all individual values listed in the dimension column.
The dimension values presented just now show the default format for the bin names. This is fixed within QlikView, but we can create our own custom format with the Replace function, as follows:
This expression will result in the following dimension values:
A solo exercise using the Class function
Now that you've seen how to create nested aggregations with the use of intervals, take a moment to create a chart to visualize the number of airlines falling into different load factor ranges with a bin width of 10 percent. Place it into the Dashboard tab.
The fascinating thing about this chart is that when the user clicks one of the bars corresponding to a specific interval, all carriers that fall into that interval will be automatically selected. This selection would be made on the Carrier Name field, as the Load Factor % bins do not exist as a field in the data model. This combination of interactivity, associations, and complex calculations on the fly allows for further navigation and extends the discovery experience.
The Pick function
Another interesting and powerful conditional function available in QlikView is the Pick function. In a way, it can be said to act as a simplified nested If. The parameters this function takes are:Pick(n, exprl, expr2)
Where n is an integer number that determines which of the subsequent expressions should be evaluated. exprl is an expression to be evaluated when n= 1 and expr2 is an expression to be evaluated when n= 2
The same result of the Pick function can be accomplished using a nested If; for example:If(n = l,exprl, If(n = 2, expr2))
However, we can easily see that the Pick function is much simpler to use in this case and can even be lighter in terms of resource usage.
Let's look at a practical example.
Using Pick with Dimensionality
One of the examples we described using the Aggr function required us to make a sub aggregation in the chart's expression to obtain the average load factor per airline per route. In the example, we used only one chart dimension, Airline, and the corresponding Aggr function only had two fields in the dimensions parameter: Route and Airline. However, the defined expression will not work as expected when a new dimension is added to the chart.
For instance, if we were to use a pivot table with several different dimensions, and in which the active dimensions are dynamically being expanded or collapsed, the sub-aggregation used to calculate the average load factor should be adapted with each new dimension arrangement; the correct aggregation expression will depend on which dimensions are visible in the pivot table.
To account for the different possible arrangements in the chart's dimensions, we will make use of the Pick function in conjunction with the Dimensionality function and the Aggr expression we previously used.
The Dimensionality function is used in pivot tables to indicate which level of aggregation is active in the pivot table for each of its segments or rows. For instance, if all dimensions are collapsed and only the first dimension is visible, then the Dimensionality function would return 1; if the first dimension is expanded, the Dimensionality function would return 2, and so forth.
The result of the Dimensionality function is row-specific, so we could have one row with one level of aggregation (depending on which of its dimensions are expanded) and another row with a different level. The Dimensionality function will account for each rows' aggregation level to provide the correct result. The following screenshot illustrates this concept, with the result of the Dimensionality function presented as the second expression column and color-coded for easier understanding:
So, to approach the presented scenario (of course, this will only keep working when users do not modify the pivot table by dragging dimensions), start by activating the Aggrsheet in the workspace and creating a pivot table with the following dimensions: Flight Type, Carrier Group, and Carrier Name, in that order. Then, enter the following expression:
Label the created expression as Avg Load Factor per Route. Then, create another expression and enter the following:$(eLoadFactor)
Label the new expression as Direct Load Factor. Then, navigate to the Presentation dialog window and enable the Show Partial Sums option for all three dimensions.
The resulting chart will be:
We can compare the result from both expressions in this screenshot. A higher result in the Direct Load Factor % column means the low-occupancy routes have just a few flights that don't affect the overall result. However, the impact of those routes can still be seen in the Avg Load Factor per Route column since that's where all routes are equally accounted for, no matter the amount of flights performed.
For each Dimensionality level, the chart depicted earlier is using a different subaggregation to calculate the average load factor per route. Additionally, when Dimensionality is zero (the total row at the bottom), the chart is calculating the average for all routes, all carriers, and all flight types. We used Dimensionality() + as the n parameter of the Pick function because otherwise there would be no way of adding an expression for when Dimensionality() equals zero.
Since the expression's definition is based on a certain, predefined, arrangement of the dimensions in our pivot table, we must be cautious when using an expression like this because it will yield unexpected results when the dimensions are re-ordered by the user (for by dragging the corresponding columns).
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.