topic Interval Match in QlikView

Interval Match

ajithsachin — Mon, 18 Mar 2013 09:56:14 GMT

Hi All,

i have two tables as below mentioned.

MARK:

FILL:

VENDOR	VARIANCE
1	6

in my load script i need to get the mark as per the variance percentage from the FILL table

means i need the result like this.

VENDOR	VARIANCE	MARK
1	6	90

because variance is in between 5 and 10 so need to give 90 marks.

how i can write a script for this while loading the data..can anybody help please..

Regards,

Ajith

Gysbert_Wassenaar — Mon, 18 Mar 2013 10:03:46 GMT

Try using a mapping table instead of intervalmatch:

MarkMap:

load * inline [

Variance, Mark

0, 100

5, 100

10, 90

15, 80

];

Fill:

load Vendor, Variance, applymap('MarkMap', ceil(Variance,5)) as Mark

from ...mysource...;

ajithsachin — Mon, 18 Mar 2013 10:24:19 GMT

Thanks for ur reply..

we cannot hardcode ceil(Variance,5) because range may be of different scale.

and this applymap function also not giving me the mark.

hic — Mon, 18 Mar 2013 14:14:00 GMT

You could do it the following way:

Create intervals in you "Marks" table (see http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/25/creating-intervals-from-change-dates how to do this)
Load Your "Fill" table as it is
Use IntervalMatchto create a third table to link the two above tables
IntervalMatch (VARIANCE) Load From, To Resident Marks;

HIC

ajithsachin — Tue, 19 Mar 2013 14:39:20 GMT

It is working fine..