Qlikview IntervalMatch

By definition in Qlikview Help:  The Qlikview IntervalMatch prefix is used to create a table matching discrete numeric values to one or more numeric intervals.

It must be placed before a Load or Select (SQL)statement that loads the intervals. The field containing the discrete data points must already have been loaded into QlikView before the statement with the IntervalMatch prefix.

Important note:  The intervals may be overlapping and the discrete values will be linked to all matching intervals.

Using Qlikview IntervalMatch is helpful because it allows you to quickly define a lookup range (via inline table or from outside source) to segment a numeric value (score, points, date, etc.) into more meaningful analysis.  It is also more efficient from a data model perspective.

How to implement Qlikview IntervalMatch

 

PlayerData:
LOAD * INLINE [
Player, FPTS
Anthony Dixon, 6
CJ Spiller, 12
EJ Manuel, 18
Frank Summers, 1
Fred Jackson, 7
Mike Williams, 4
Robert Woods, 8
Sammy Watkins, 3 ];

 

Your data model will look like this:

 

Qlikview IntervalMatch

 

Tablebox:

 

Qlikview IntervalMatch

 

Now, go back into the LOAD SCRIPT and add the following, below the INLINE table script you inserted above:

FPTSLookup:
LOAD * INLINE [
FPTSMin, FPTSMax, FPTSLookupValue
0, 4, <5
5, 9, 5-10
10, 14, 10-15
15, 100, >15
];

IntervalMatch:
INTERVALMATCH (FPTS)
LEFT JOIN LOAD Distinct FPTSMin, FPTSMax
RESIDENT FPTSLookup;

LEFT Join (PlayerData) LOAD * RESIDENT FPTSLookup;

DROP TABLE FPTSLookup;

 

Run another reload and your data model will now look like this:

 

Qlikview IntervalMatch

 

Tablebox:

 

Qlikview IntervalMatch

 

Conclusions

As you can see, the FPTS field is now assigned a lookup value FPTSLookupValue.  You can now filter and analyze this value.  Setting up the IntervalMatch is a simple and efficient way to add these types of fields to your data model, and keep your data model optimized and efficient.

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

2 thoughts on “Qlikview IntervalMatch

  1. Hello,
    Thanks for your helpful and professional posts.
    I did all steps as you mentioned but i didnt get the result that you presented as a result,
    may i did some wrong:
    Player FPTS FPTSLookupValue
    Anthony Dixon 6 –
    CJ Spiller 12 –
    EJ Manuel 18 –
    Frank Summers 1 15
    Fred Jackson 7 –
    Mike Williams 4 15
    Robert Woods 8 –
    Sammy Watkins 3 15

    • Hello Cyrus, for some reason, the load script details in my original post were not copying over properly. I went back and updated the post, to reflect the proper LOAD SCRIPT text. Please try it again and it should work.

Leave a Comment