topic Re: IntervalMatch multiple key in QlikView

IntervalMatch multiple key

MindaugasBacius — Fri, 06 Jun 2014 06:41:57 GMT

Howdy,

I face up a problem connecting two tables with IntervalMatch:

Grades:

load * inline

[

min, max, grade, subject

0, 20, fail, math

20, 50, bad, math

50, 80, good, math

80, 100, perfect, math

0, 20, fail, history

20, 50, bad, history

50, 100, good, history

];

Marks:

load * inline

[

mark, student, sub

6, steve, math

49, ema, math

77, cate, math

25, joe, math

98, hary, history

];

Data:

IntervalMatch (mark, sub) load

min, max, subject

resident Grades;

How to connect correctly Grades and Marks tables in IntervalMatch that I could get the information like this:

6, steve, math, fail

49, ema, math, bad

77, cate, math, good

25, joe, math, bad

98, hary, history, good

Thank you in advance!

Re: IntervalMatch multiple key

its_anandrjs — Fri, 06 Jun 2014 07:24:46 GMT

Try to load the table this way ans rename the sub field as subject in the Marks table thats why interval match not works

Marks:

LOAD *,sub as subject;

load * inline

[

mark, student, sub

6, steve, math

49, ema, math

77, cate, math

25, joe, math

98, hary, history

];

Grades:

load * inline

[

min, max, grade, subject

0, 20, fail, math

20, 50, bad, math

50, 80, good, math

80, 100, perfect, math

0, 20, fail, history

20, 50, bad, history

50, 100, good, history

];

Inner Join

Data:

IntervalMatch (mark, subject) load min, max, subject resident Grades;

Join(Marks)

LOAD * Resident Grades;

DROP Table Grades;

DROP Field sub;

You get this output:-

Re: IntervalMatch multiple key

MindaugasBacius — Fri, 06 Jun 2014 08:01:48 GMT

It worked perfectly!