Do: AGGR Function Exercise
7 SCRIPTING AND DATA MODELING CHALLENGES
7.1 Link Tables and Concatenated Tables
Link tables combine information that has different levels of detail or fre-quency. Generally, they are best used when the metrics being measured do not have the same base of time, perhaps one is an aggregation over a time period, i.e., by Month. Link tables are also used when data does not meet the business requirements of a calculation. So you can think of them in terms of their name. They are useful to link information that would not oth-erwise be automatically linked with QlikView’s associative logic.
In other words, when loading data, certain scenarios can occur whereby you need to store fact type information in different tables, such as transactions separated from budgets. Each type of fact may need to join with multiple common dimensions (such as Time and Product) which would then cause loops in the datastructure. To circumvent such scenarios, we create linking tables that store the individual ways that each fact table joins to the common dimensions.
7.1.1 Concatenation
Concatenation can be used to reduce the number of fact tables in a structure if there is sufficient commonality between the fact tables to validate such a combination. This can make a data model more efficient.
When fact tables contain the same granularity of information and a com-mon key is found between both facts, an outer join operation is preferable to a concatenate operation, as the output records would be linked on a record-by-record basis and hence single record operation will be possible.
Objectives
• Understand the use link tables
• Calculate net change within a field
• Use advanced functions for handling time
• Know why dynamic aggregation and interval matching are important
64
QlikView Developer II |7 SCRIPTINGANDDATAMODELINGCHALLENGES
We can also see what has been loaded in the load process with the previ-ous()function. The previous()function can only be used to look one record back in the load process. If you need to look further back than one record in the load process, you have to nest the previous() function as in:
previous(previous(myRecord))
This can be problematic and cumbersome when you want to see the total net change or percent change.
Let us assume we need to count the number of units that are at a given site within a week and need to find the percent of utilization for a unit at a given site.
If the units are at multiple sites but return to the same site within the same week, peek()and previous() can work but require complex coding with FORloops or RESIDENTloads. However, we can get around that by grouping the information from a tracking table and Left Joining the result back into the tracking table.
The steps in the exercise demonstrate the difference.
7.3 Functions InDate, Data & DateIslands
This section includes concepts used to create documents: InDate and Data/
DateIslands.
Data/DateIslands are tables that are not directly linked with other tables (tables that are independent of any explicit links). You can think of this as an array of values that can change with a selection by the user and will force new results to be displayed in the Qlikview Data Model via a non-key field connection.
However, when using this approach you will not change the selection state in the underlying QlikView Data Model. This is usually used for KPIs so that, when moving to a tab with more detail, the user has a full view and selection of data for filtering.
If there is a selection made in the QlikView Data Model outside of a Data/
DateIsland
,
that select will remove data used in the calculation of a KPI and could result in blank or misleading KPI displays.The InDate()functions are used in conjunction with the Data/DateIsland.
7.4 Aggr()
Note: If you have already completed Chpater 6, skip this explanation.
Aggr() is an advanced function that allows you to calculate an aggregation of multiple dimensions. As a rule, you can not aggregate an aggregated
QlikView Developer II |7 SCRIPTINGANDDATAMODELINGCHALLENGES
expression (nested aggregations). The exception to this restriction is to use aggr()
;
the format is:aggr([distinct | nodistinct] expression {,dimension})
The best way to think of aggr()is that, by using this function, you can cre-ate a mini QlikView data model on the fly, without needing to edit your data load script or reload from the source data for your QlikView file. It allows for nested aggregation. You can add more dimensions than are represented in charts such as a Pivot Table.
Aggr() is used in the user interface, rather than in the load script. Typically, it is used in a chart object expression, as we saw in the previous chapter.
7.5 Class()
Class()is an aggregation function that can be used to create buckets of information similar to what you would create in an Accounts Receivable document showing aged accounts. Another way of thinking of Class() is that you can group your dimension values.
7.6 Dynamic Aggregation
In QlikView, you can assign a field value to a variable. Subsequently, you can use the value that the variable holds in an expression. This is similar to a pointer in C-programming or other similar programming languages.
QlikView calculates information on the fly. With this approach and using a table to hold expressions, you can create a reference to the formulas located in the table which, in turn, effectively creates a library of expressions that can then be used to dynamically change your display of information.
The user will be able to cycle through the KPIs by selecting choices in a List Box.
7.7 IntervalMatch
The IntervalMatch script statement facilitates the mapping of dates to peri-ods or records to slowly changing dimensions, useful for creating a fully
66
QlikView Developer II |7 SCRIPTINGANDDATAMODELINGCHALLENGES
memory and User Interface performance can suffer. One solution to explore is to LEFT JOIN the IntervalMatch() table into a parent table.
Here is a small example of IntervalMatch(). We are maintaining the approach of keeping the examples small so that they can be easily verified.
In this exercise we will use IntervalMatch() to find the hours that an employee is working on-site.
QlikView Developer II |EXERCISE