IntervalMatch Function in QlikView Scripting
IntervalMatch is used to link a discrete numeric value to one or more numeric intervals. QlikView IntervalMatch is a powerful function used to match distinct numeric values to numeric interval. It is useful in analyzing how the events actually happened versus the planned events.
This function matches a numeric value in one table, such as date, timestamp, score, percent, or any number to an interval or duration in another table. This can be useful in examples such as defining student grades, sales quarters, scoring and metric evaluation and productivity. The IntervalMatch function discrete data to one or more dimensions that are changing over time. It can be used for resolving slowly changing dimensions by linking the specific key fields to the appropriate numeric intervals. It can be used with LOAD, Inline and SQL select statements.
IntervalMatch saves time and memory because joins are not needed for intervalMatch, the time to reload is shorter because memory demands and processing needs are less.
The use of intervalMatch is done after setting up a table containing interval durations (and some other defining attributes such as test scores, percentages, and corresponding grade marks of 90-100), as well as an event table (with test scores, number of calls, sales figures, or other numeric measures). IntervalMatch will generate all the different combinations of intervals and events. IntervalMatch creates a table of its own, as well as synthetic table with all the combinations of intervals and events.
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. Load this script in qlikview and run it to examine the output as shown in the following code:
0, 20, poor
21, 30, fair
31, 40, good
41, 50, excellent
51, 100, out standing
LOAD * INLINE [
IntervalMatch (calls) LOAD
- 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.
When this script is run, a synthetic key is created (as designed-do not correct it), and you may create a table box in the application with the name of the salesperson, number of calls, and performances score in it as shown in the following diagram:
And here is the resulting data model, note the synthetic keys present in internal table view in table viewer. This is the correct functionality- of how a synthetic table is created; leave this $ syn table in place as shown in the following diagram:
A major and important point is that when we apply the IntervalMatch function to tables, it is necessary that the tables have numeric values.
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.
- It avoids the case statements, concepts.
Enroll for Live Instructor Led Online QlikView Training