topic Re: Extended IntervalMatch in QlikView

Extended IntervalMatch

madhumitha — Tue, 17 Oct 2017 07:19:19 GMT

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_Nam	Subj	Marks	Grade
S1	Math	31	C
S2	Math	45	B
S3	Math	62	B
S4	Math	82	A
S5	Math	90	A
S1	Science	21	D
S2	Science	35	D
S3	Science	52	C
S4	Science	72	B
S5	Science	83	B

Re: Extended IntervalMatch

oknotsen — Tue, 17 Oct 2017 07:35:28 GMT

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;

Re: Extended IntervalMatch

swuehl — Tue, 17 Oct 2017 07:42:08 GMT

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;

Re: Extended IntervalMatch

antoniotiman — Tue, 17 Oct 2017 07:46:13 GMT

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
];

Re: Extended IntervalMatch

madhumitha — Tue, 17 Oct 2017 09:39:23 GMT

Thanks Antonio. This exactly fetches me the desired result.

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

Re: Extended IntervalMatch

antoniotiman — Tue, 17 Oct 2017 10:21:53 GMT

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.

Re: Extended IntervalMatch

oknotsen — Wed, 18 Oct 2017 10:07:35 GMT

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.