Interval Match

[ajithsachin]

Hi All,

     i have two tables as below mentioned.

     MARK:

MARK	VARIANCE_PERCENTAGE
100	0
100	5
90	10
80	15

     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]

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]

Dear Gysbert Wassenaar,

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 · Accepted solution]

You could do it the following way:

1. 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)
2. Load Your "Fill" table as it is
3. Use IntervalMatchto create a third table to link the two above tables
   IntervalMatch (VARIANCE) Load From, To Resident Marks;

HIC

[ajithsachin]

Thanks Henric Cronström

It is working fine..