Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jblomqvist
Contributor II

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

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?

6 Replies
MVP
MVP

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

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)

Not applicable

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

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;

jblomqvist
Contributor II

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

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

Not applicable

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

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.

MVP
MVP

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

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;

Arjunarao
Honored Contributor II

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

Hi Please check this as well.

Interval Match Feature/Function

Community Browser