how to use Interval Match for following requirement

[vikasmahajan]

Dear All

I have following fact table and slabs table

%COGS	%Gr Margin	COGS	PordCode	ProdName	Product Code	Sls Amt
20	80	35	FDCEBT001	CYCLOPAM TAB - SALE 10'S	1CY4	173

this are fact table and following are range table I want to use interval match if  my [%Gr Margin ] field falling under (range fr To) then

respective product class should attached. ( for E.g  1CY4 will be getting Class -> A2 ( becase 1cy4 falls in (80-89) )

I have attached QVW file also.

Product_Class	RANGE_FROM	RANGE_TO
A1	90	999999999
A2	80	89
A3	70	79
B4	60	69
B5	50	59
C1	40	49
C2	30	39
C3	20	29
C4	10	19
C5	0	9

Thanks

Vikas

[vikasmahajan]

Can you check now i create dummy excel and qvw Why 1cy4 is not getting product Class

Vikas

Thanks a Lot for reply

[vikasmahajan]

Hello

Many thanks for reply finally I resolved issue , there was rounding problem in 1CY4  in TO_SLAB 79.99 but qlikview shows 80

[ali_hijazi]

yes I was about to inform you of this

put in the slabs sheet instead of 70 to 79 , 70 to 79.99999 and so on

and it will work just fine

[#5]

Hi Vikas,

IntervalMatch can definitely achieve what you want, while I want to implement it from the intervalmatch concept.

The IntervalMatch, the key to join or not is whether it's value is between the numeric range. Therefore, calculate the  FABS([RANGE_FROM] - [%Gr Margin]) + FABS([RANGE_TO] - [%Gr Margin]) can be used for conditional statements.

Attached is the implement, which is based on yours. While I prefer to use Ali's because of simplicity.

Alan

[vikasmahajan]

Thanks Mr.Yang.

Vikas