Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

madhumitha
Contributor

Extended IntervalMatch

Hello all,

Below is my script for extended interval match and the expected output. its simple not working and I can't figure out why. Could you please help?

Range:
LOAD * INLINE [
Max range, Min Range, Grade,Subj
1, 20, D,Math
21, 40, C,Math
41, 80, B,Math
81, 100, A,Math
1, 40, D,Science
41, 60, C,Science
61, 90, B,Science
91, 100, A,Science
]
;
 
Marks:
Load * Inline [
Stud_Nam,Marks, Subj
S1,31,Math
S2,45,Math
S3,62,Math
S4,82,Math
S5,90,Math
S1,21,Science
S2,35,Science
S3,52,Science
S4,72,Science
S5,83,Science
]
;
Inner join
IntervalMatch(Marks,Subj) load [Max range] as MaxRange, [Min Range] as MinRange resident Range;

expected output:

   

Stud_NamSubjMarksGrade
S1Math31C
S2Math45B
S3Math62B
S4Math82A
S5Math90A
S1Science21D
S2Science35D
S3Science52C
S4Science72B
S5Science83B

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Extended IntervalMatch

Range:

LOAD * INLINE [

Max range, Min Range, Grade,Subj

1, 20, D,Math

21, 40, C,Math

41, 80, B,Math

81, 100, A,Math

1, 40, D,Science

41, 60, C,Science

61, 90, B,Science

91, 100, A,Science

];

 

Marks:

Load * Inline [

Stud_Nam,Marks, Subj

S1,31,Math

S2,45,Math

S3,62,Math

S4,82,Math

S5,90,Math

S1,21,Science

S2,35,Science

S3,52,Science

S4,72,Science

S5,83,Science

];

Inner join

IntervalMatch(Marks,Subj) load [Max range], [Min Range], Subj resident Range;

LEFT JOIN

LOAD * RESIDENT Range;

DROP TABLE Range;

6 Replies
oknotsen
Honored Contributor III

Re: Extended IntervalMatch

You are forgetting to load "Subj" in your last load.

It should be:

Inner join
IntervalMatch(Marks,Subj) load [Max range] as MaxRange, [Min Range] as MinRange, Subj resident Range;


I would also add this:

drop Table Range;

May you live in interesting times!
MVP
MVP

Re: Extended IntervalMatch

Range:

LOAD * INLINE [

Max range, Min Range, Grade,Subj

1, 20, D,Math

21, 40, C,Math

41, 80, B,Math

81, 100, A,Math

1, 40, D,Science

41, 60, C,Science

61, 90, B,Science

91, 100, A,Science

];

 

Marks:

Load * Inline [

Stud_Nam,Marks, Subj

S1,31,Math

S2,45,Math

S3,62,Math

S4,82,Math

S5,90,Math

S1,21,Science

S2,35,Science

S3,52,Science

S4,72,Science

S5,83,Science

];

Inner join

IntervalMatch(Marks,Subj) load [Max range], [Min Range], Subj resident Range;

LEFT JOIN

LOAD * RESIDENT Range;

DROP TABLE Range;

Highlighted
antoniotiman
Honored Contributor III

Re: Extended IntervalMatch

May be this

Range:
LOAD *,[Max range]+IterNo()-1 as Marks INLINE [
Max range, Min Range, Grade,Subj
1, 20, D,Math
21, 40, C,Math
41, 80, B,Math
81, 100, A,Math
1, 40, D,Science
41, 60, C,Science
61, 90, B,Science
91, 100, A,Science
]
While [Max range]+IterNo() <= [Min Range];

Marks:
Inner Join
Load * Inline [
Stud_Nam,Marks, Subj
S1,31,Math
S2,45,Math
S3,62,Math
S4,82,Math
S5,90,Math
S1,21,Science
S2,35,Science
S3,52,Science
S4,72,Science
S5,83,Science
]
;

madhumitha
Contributor

Re: Extended IntervalMatch

Thanks Antonio. This exactly fetches me the desired result.

Is this scenario shouldn't be done using interval match at all?

antoniotiman
Honored Contributor III

Re: Extended IntervalMatch

I prefer this way.

Intervalmatch creates table Bridge between Your Tables.

IterNo() generates Rows in Table.

However Stefan's answer is correct way to use Intervalmatch.

oknotsen
Honored Contributor III

Re: Extended IntervalMatch

Downside of this method is that you can get a huge table in memory before your join where the table you created for the interval match stays tiny.

Your current example is only with a limited number of values, but imagine the change in size of the table between the two methods when you get a lot more values to match.

So to save memory during the reload and likely higher performance, I would still stick to the interval match.

If the amount of matches stays tiny and performance is far from an issues, it doesn't matter of course.

May you live in interesting times!