topic Re: Understanding the rules of Interval Match function, value and range must be loaded first? in QlikView

Understanding the rules of Interval Match function, value and range must be loaded first?

jblomqvist — Thu, 13 Aug 2015 19:44:34 GMT

Hi all,

I am trying to understand how to use Interval Match function.

For example I have a Student Grades table with StudentID, CourseID, Grade.

I would like to compare the Grade value against Grade Range.

Grade Range is in the GradeRange table with CourseID, GradeStart, GradeStop, Range as fields.

In order to do Interval Match is it true that I must have those two tables created first to do the analysis?

So my intervalmatch table would be:

IntervalMatch(Grade)

Load GradeStart, GradeStop Resident GradeRange;

Can the Load table after IntervalMatch have more than two fields?

Re: Understanding the rules of Interval Match function, value and range must be loaded first?

maxgro — Thu, 13 Aug 2015 19:54:49 GMT

did you read this useful post?

IntervalMatch‌

Can the Load table after IntervalMatch have more than two fields?

yes, it can

search in QlikView Help interval match and choose IntervalMarch (extended)

Re: Understanding the rules of Interval Match function, value and range must be loaded first?

Thu, 13 Aug 2015 20:00:18 GMT

John, if your range will change from CourseID you need to use Extended syntax like below:

Grades:

LOAD CourseID , StudentID , Grade From s1;

GradeRange:

Load CourseID, GradeStart, GradeStop, Range From s2;

Inner Join(GradeRange)

IntervalMatch(Grade,CourseID)

LOAD GradeStart, GradeStop , CourseID Resident GradeRange;

Re: Understanding the rules of Interval Match function, value and range must be loaded first?

jblomqvist — Thu, 13 Aug 2015 20:10:09 GMT

Hi dathu and Massimo,

What is the purpose for the Extended version of Interval Match? I tried reading the Help but could not imagine a relating example.

Have you got an example where extended is needed? Thanks

Re: Understanding the rules of Interval Match function, value and range must be loaded first?

Thu, 13 Aug 2015 20:23:31 GMT

If you need to add Key field to you range, you need to use Extended syntax.

in above exapmle, the grade range will change from CourseID, you need to map your CourseID, Grade with CourseID, GrateStart , GreadeEnd. so you need to use Extended syntax.

Re: Understanding the rules of Interval Match function, value and range must be loaded first?

maxgro — Thu, 13 Aug 2015 20:47:32 GMT

extended because there is

- the field and the range to check (OrderDate, DateFrom, DateUntil) as in the non extended interval match

- another field to use for joining the 2 tables (Val)

SalesData:

LOAD * INLINE [

Orderline, OrderDate, Val , amount

1, 20100115,EUR,14.00

2, 20100215,USD,2.00

3, 20100315,EUR,10.00

4, 20100415,USD,3.00

5, 20100515,USD,4.00

6, 20100615,USD,5.00

7, 20100715,GBP,6.00

];

RateData:

LOAD * INLINE [

Val , DateFrom , DateUntil,Rate

USD, 20100101,20100331,1.25

USD, 20100401,20100630,1.29

GBP, 20100101,20100930,1.34

EUR, 20000101,99991231,1

];

INNER JOIN INTERVALMATCH (OrderDate, Val)

LOAD

DateFrom,

DateUntil,

Val

resident RateData;

Re: Understanding the rules of Interval Match function, value and range must be loaded first?

qlikviewwizard — Fri, 14 Aug 2015 00:40:26 GMT

Hi Please check this as well.

Interval Match Feature/Function