QlikView IntervalMatch:

IntervalMatch is a powerful function used to match distinct numeric values to numeric intervals. It is useful in analyzing how the events actually happened versus the planned events. The example of a scenario where it is used is in the assembly lines of the production houses where the belts are planned to run at certain times and for certain duration. However, the actual run can happen at different points in time because of breakdown etc.

Example

Here’s a sample scenario of a sales call center that has targeted call quotas for the sales persons, where lower numbers of outgoing calls per day is bad whereas the higher number of outgoing calls per day is good.

We open the script editor in a new QlikView document using Control+E. The following code creates the required tables as inline data. After creating this script, press control+R to reload the data into the QlikView document.

Syntax:

Performance:
LOAD   *   INLINE  [
ID, Calls,   Performance
0,     20,   poor
21,    30,   fair
31,    40,   good
41,    50,   excellent
51,    100,  out standing
];
Actual-sales-calls:
LOAD   *   INLINE  [
      Calls,   salesperson
      40,     henric
      89,     gerard
      21,     felton
      46,     tumas
       11,    franc
];
Interval bridge:
IntervalMatch (calls) LOAD
Calls-min,  calls-max
RESIDENT   performance;

  • The first load statement defines the performance table (this is the interval table).
  • The second load statement defines the actual-sales-calls table (actual sales calls per day-the events table).
  • The third section is the intervalMatch section that ties the calls data field in actual-sales-calls to the calls-min and calls-max metrics in the performance table.

Creating Sheet Object

Let us create a Table Box sheet object to show the data generated by the IntervalMatch function. Go to the menu item Layout → New Sheet Object → Table Box. The following window appears in which we mention the Title of the table and select the required fields to be displayed.

Note

A major and important point is that when we apply the IntervalMatch function to tables, it is necessary that the tables have numeric values.

Advantage

There are many given advantages of the IntervalMatch function.

  • Save time: It saves the time of the QlikView application because joins are not used with the IntervalMatch function.
  • Save memory: It saves the memory of the QlikView application because it avoids loading all the possible numeric values into a fact table.