IntervalMatch

[hic]

A common problem in business intelligence is when you want to link a number to a range. It could be that you have a date in one table and an interval – a “From” date and a “To” date – in another table, and you want to link the two tables. In SQL, you would probably join them using a BETWEEN clause in the comparison.

But how do you solve this in QlikView, where you should avoid joins?

The answer is to use IntervalMatch.

IntervalMatch is a prefix that can be put in front of either a Load or a SELECT statement. The Load/SELECT statement needs to contain two fields only: the “From” and the “To” fields defining the intervals. The IntervalMatch will generate all the combinations between the loaded intervals and a previously loaded numeric field.

Typically, you would first load the table with the individual numbers (The Events), then the table with the Intervals, and finally an intervalmatch that creates a third table that bridges the two first tables.

Events:
Load * From Events;

Intervals:
Load * From Intervals;

IntervalMatch:
IntervalMatch (Date)
Load distinct FromDate, ToDate resident Intervals;

The resulting data model contains three tables:

1. The Events table that contains exactly one record per event.
2. The Intervals table that contains exactly one record per interval.
3. The IntervalMatch table that contains exactly one record per combination of event and interval, and that links the two previous tables.

Note that this means that an event may belong to several intervals, if the intervals are overlapping. And an interval can of course have several events belonging to it.

This data model is optimal, in the sense that it is normalized and compact. All QlikView calculations operating on these tables e.g. Count(EventID) will work and will be evaluated correctly. This means that it is not necessary to join the intervalmatch table onto one of the original tables. Joining it onto another table may even cause QlikView to calculate aggregations incorrectly, since the join can change the number of records in a table.

Further, the data model contains a composite key (the FromDate and ToDate fields) which will manifest itself as a QlikView synthetic key. But have no fear. This synthetic key should be there; not only is it correct, but it is also optimal given the data model. You do not need to remove it.

IntervalMatch can also be used with an additional key between the tables – i.e. when you have Slowly Changing Dimensions. But more about that in a later post.

HIC

For more on IntervalMatch and some script examples, see the technical brief IntervalMatch and Slowly Changing Dimensions.

[hic]

Yes, but... What do you want to do?

Let's say that you have a Date which has a match in Interval1, but not in Interval2. What should you then do? And the next Date has a match in Interval2 but not in Interval1. And the third date has a match in both. What then? Do you want to link all three dates the same way?

It sounds to me as if you may should consider two tables with intervals instead of one. Or a bridge table listing all intervals.

HIC

[#3]

Helllo Henric,

Thank you for reply.

Let me explain my case.

Example:

I have Two Table. Event and Interval as follow.

Events:
Load FromDate_1, ToDate_1, FromDate_2, ToDate_2, Point
From Events;

Intervals:
Load Date1, Date2
From Intervals;

Now i want to compare

Date1 with FromDate_1 and ToDate_1 and

Date2 with FromDate_2 and ToDate_2

It means

Date1 >= FromDate_1 and Date1 <= ToDate_1 and

Date2 >= FromDate_2 and Date2 <= ToDate_2

If this condition match I get Point.

Can i write statement like follow?

IntervalMatch:
IntervalMatch (Date1, Date2)
Load distinct FromDate_1, ToDate_1, FromDate_2, ToDate_2 resident Intervals;

Can you please help me in this?

Thanks.

[hic]

You cannot write the intervalmatch the way you suggest.

The following script should do it.

HIC

// =======================================

Intervals:

Load

          FromDate_1 & '|' & ToDate_1 & '|' & FromDate_2 & '|' & ToDate_2 as IntervalID,

          FromDate_1, ToDate_1, FromDate_2, ToDate_2, Point

From Intervals;

DatePairs:

Load

          Date1 & '|' & Date2 as DatePairID,

          Date1, Date2

From DatePairs;

tmpBridgeTable:

Load Date1, Date2

Resident DatePairs;

Left Join

IntervalMatch (Date1)

Load distinct FromDate_1, ToDate_1 Resident Intervals;

Left Join

IntervalMatch (Date2)

Load distinct FromDate_2, ToDate_2 Resident Intervals;

BridgeTable:

Load

          FromDate_1 & '|' & ToDate_1 & '|' & FromDate_2 & '|' & ToDate_2 as IntervalID,

          Date1 & '|' & Date2 as DatePairID

Resident tmpBridgeTable;

Drop Table tmpBridgeTable;

[vikasmahajan]

Sir,

Very Useful document...

Vikas

[aveeeeeee7en]

Very Useful.

Can you Please help me on this :

Re: Location Wise Different Holidays

[#7]

Henric, thanks for the example. Very helpful. I'm sure it is a "data thing" but when I look at my Table Viewer I see that QlikView has created a synthetic table between my IntervalMatch table and the table with the Interval data. In my case I am using a calendar table for the dates and an employee assignment table with FROM and TO dates. Is there any way to eliminate the synthetic table? Or do I just live with it as the results appear correct?!

Thanks

Kevin

[hic]

The synthetic key in a standard intervalmatch is not only harmless, but in fact quite efficient. So there is no reason to remove it really.

But if you still want to remove it, you should create a composite key like

     FROM & '|' & TO as Interval

both in the Intervals table and in the IntervalMatch table. To do that, you would need to run a second pass through the IntervalMatch table (creating a new table) and then drop the original one. See more in http://community.qlik.com/docs/DOC-4310

HIC

[#9]

Henric, I have a problem that is similar to what you were explaining in this post, but I could not solve it yet. I have an Excel file with range date that I need to transform into single rows with single dates and their respective affiliate and cost_per_registration .  The end_date 'if null' means today(2014-01-24), and country_name 'else' means the other countries.This is the table I have:

And, this is what I want to get:

It would be great if you could help me with this problem.

Thanks

[hic]

Although it is possible to use IntervalMatch for this, I would use a different approach: A while loop that generates all the dates in the range. See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/12/reference-dates

HIC

[#11]

Thank you! clear & complete!