topic Re: When would you use Interval Match (extended) version over the normal Interval Match? in QlikView

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

jblomqvist — Mon, 08 Feb 2016 19:33:55 GMT

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?

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

swuehl — Mon, 08 Feb 2016 19:36:03 GMT

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

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

MK_QSL — Mon, 08 Feb 2016 19:43:27 GMT

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..

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

swuehl — Mon, 08 Feb 2016 19:50:37 GMT

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

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

jblomqvist — Fri, 12 Feb 2016 10:52:38 GMT

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!

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

swuehl — Fri, 12 Feb 2016 11:03:42 GMT

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.

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

Peter_Cammaert — Fri, 12 Feb 2016 11:14:49 GMT

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

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

jblomqvist — Fri, 12 Feb 2016 11:15:23 GMT

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

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

Peter_Cammaert — Fri, 12 Feb 2016 11:17:42 GMT

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.

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

jonathandienst — Fri, 12 Feb 2016 11:23:45 GMT

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

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

swuehl — Fri, 12 Feb 2016 11:36:15 GMT

John Blomqvist wrote:

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

The really nice feature using QV is that's really easy to play around with samples.

Just copy the code from the HELP page and run the script, once with additional key and once without.

I believe there is nothing better than to actually see what happens.