Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jblomqvist
Specialist
Specialist

When would you use Interval Match (extended) version over the normal Interval Match?

Hello,

I am new to QlikView and I was reading about Interval Match extended.

Can anybody tell me when would you use Interval Match (extended) version over the normal Interval Match?

They don't look too different to each other so I would like to know when to use each one!

I have already read the Help and Reference Manuel but I still don't understand. Can anybody please simplify with an example?

10 Replies
swuehl
MVP
MVP

The extended version is used when you need an additional key field to distinguish between different ranges that match your numeric value.

MK_QSL
MVP
MVP

This is intervalmatch...

Hair_Disaster:

LOAD * INLINE [

    Min_Percentage, max_Percentage, Kerotine_Value

    40, 60, Low

    61, 70, Medium

    71, 80, Normal

    81, 90, High

    92, 100, Above All

    -10, 0, Negative

];

Hair_Shine:

LOAD * INLINE [

    Percentage, Name

    45, Anand

    55, Karim

    65, Islam

    70, Ancy

    92, Priya

    110, Manish

    500, Tarjani

];

Left Join

IntervalMatch(Percentage) Load

Min_Percentage, max_Percentage

Resident Hair_Disaster;

Left Join (Hair_Shine)

Load * Resident Hair_Disaster;

Drop Table Hair_Disaster;

=====================================

This is extended intervalmatch example..

Orders:

LOAD

  Date(Date#(OrderDate,'DD-MM-YYYY')) as OrderDate,

  Item,

  Quantity

INLINE

[

  OrderDate, Item, Quantity

  01-01-2013, X0001, 12

  31-12-2013, X0001, 12

  01-02-2014, X0001, 15

];

Temp:

LOAD

  Date(Date#(StartDate,'DD-MM-YYYY')) as StartDate,

  Date(Date#(EndDate,'DD-MM-YYYY')) as EndDate,

  Item,

  Price

INLINE

[

  StartDate, EndDate, Item, Price

  01-01-2013, 31-12-2013, X0001, 3.45

  01-01-2014, 31-12-2014, X0001, 3.55

];

Inner Join

IntervalMatch(OrderDate, Item)

LOAD DISTINCT StartDate, EndDate, Item Resident Temp;

Left Join(Orders)

LOAD * Resident Temp;

Drop Table Temp;

=================================

Swuehl has already explained the difference..

swuehl
MVP
MVP

Well, maybe not enough.

Don't know what to add to the examples that are contained in the help (there are examples for both versions when looking at the IntervalMatch() help page):

QlikView ‒ IntervalMatch

jblomqvist
Specialist
Specialist
Author

Hi all,

Thank you for the replies. I am still having trouble understanding why I need additional key field to distinguish between different ranges that match your numeric value.

Why would this be the case? Sorry it's not making sense to me yet!

swuehl
MVP
MVP

Have you looked into the example I linked into the HELP pages?

This example demonstrates why you would need a key for example if you have multiple production lines with events and time intervals you need to reference to specific lines.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

A simple case would be when you try to match different intervals for specific departments to an employee that has a timestamp and works in a specific department at that moment. Department would be an additional "dimension"-like value that should be taken into account when trying to match timestamps to intervals.

The explanation for IntervalMatch (Extended Syntax) in QV Help is really good, but the example is a bit thin.

Peter

jblomqvist
Specialist
Specialist
Author

Hi Swuehl yes your earlier linked helped as well as Manish's example. I am having more difficulty simplifying why key field has to be used to distinguish different ranges. What would happen if this wasn't the case?

Sorry if it sounds like a silly question, I am trying to really understand the importance of the key field

Peter_Cammaert
Partner - Champion III
Partner - Champion III

You would match a numerical value to all intervals, regardless of the keyfield value that should match 1:1. You get an overly complex synthetic key and too many matches.

jonathandienst
Partner - Champion III
Partner - Champion III

The most common application is to match a slowly changing dimension (SCD) which contains start and end dates, a value, and the item to which this dimension value applies.

As an example, consider a set of sales people that are grouped into geographical areas, which might change from time to time, and we want to report the sales by area with the sales assigned to the correct area based on the SCD. The Sales table contains a transaction date, salesperson ID, sales amount (among others). The SCD in the Intervals table would contain: sales person ID, start date, end date and area code. so we would use:

     Left Join (Sales)

     IntervalMatch(SalesDate, SalesPersonID)     // these are the fields from Sales

     LOAD StartDate, EndDate, SalesPersonID   // these are from Intervals

     Resident Intervals;

Now the sales area (in Intervals) will be correctly associated* with the sales transactions (in Sales)according to the area to which the sales person belonged in the sales date range between StartDate and EndDate.

*via the composite key StartDate, EndDate, SalesPerson between the Sales table and the Intervals table

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein