Strange interval match issue

[achates2008]

I encountered a strange interval match issue.

I am trying to get a commission rate by matching profit % with a profit range, so different profit% will have different commission rate.

In database, profit% is maintained in step, like 5, 10, 15..., I converted it to a range by adding a decimal, so I got below. If given profit fell into specific stand-end range, then I got the rate.

start, end, commission rate

0 , 5, 1%

5.0001, 10,  2%

10.0001,15, 3%

....

For most of the cases, it works properly. However, there are always some cases that it will not get a commission rate (show as null value). I tried to add a decimal 0.00001 to the given profit%. It resolved some issues, but it will still have other similar issues. It looks like a bug of the system, really have no idea for the reason. Anyone can help?

Since there is no clue in which circumstances this will happen, I don't know how to provide an example.

[StarinieriG]

Hi,

have you already tried to round the value before using interval match?

ex Round(Field,0.0001)

[sunny_talwar]

Would you be able to share a sample where we can see this? May be we can spot the cases by looking?

[achates2008]

I didn't. Do you mean to round given the profit%? What difference it will make?

[achates2008]

I guess not possible because it is in internal network, and file is very big, also sensitive.

[StarinieriG]

Because using range like that

0 5

5.0001 10

If you have a value like 5.000007, it will be null

[achates2008]

Good point! I should improve accordingly.

However, I checked the value, it is exactly in between the range.

[chriscammers]

problem with Interval match is that the condition is inclusive

It is like adding "x >=  y and x <= z" and when you use an inclusive condition to define the ranges you always end up with a gap between the ranges.

For this and some performance reasons I have stopped using the interval match functionality in favor of doing it manually.

Follow these Steps...

• Create a table with the disctinct values of the thing you are trying to evaluate with your ranges.

RangeAssignments:
Load 
   FieldValue('Your Field',Recno()) as [Your Field]
Autogenerate FeildValueCount('Your Field');

• Left Join your range buckets onto your Range Assignement table without a qualifying field. The results of this join will be a cartesean product. you should be careful because this can cause a significant increase in memory utilization.

Left Join(RangeAssignments)
Load
   BeginRange
   EndRange
   RangeDesc
Inline [
   BeginRange,EndRange,RangeDesc
   -99999999,0,Error low value <=0
   0,5,>0<=5
   5,10,>5<=10
   10,15,>10<=15
   15,9999999999,>15
];
   

• Join the table back on itself with a qualifying fields and where clause. Set your where clause so it will be consistent with the desired way you want to apply your ranges.

Inner Join(RangeAssignments)
Load
   [Your Field],
   BeginRange,
   EndRange
Resident RangeAssignments
Where [Your Field] > BeginRange and [Your Field] <= EndRange;

[achates2008 · Accepted solution]

Impressive idea! Thanks Chris! I will try also.

I would say sorry that I found this time it was my mistake. The matched result was excluded finally during left join...

On the other hand, previously I really resolved the issue by adding 0.0001, and the value should be in the range.

Anyway, I thank you all for the help!

[marcus_sommer]

An alternatively and my preferred way to resolve interval data is the use of while-loops. IMO they are easier and more flexible as the native intervalmatch-feature - especially if you avoid overlapping the intervals and used an appropriate rounding or even an integer it's a very simple approach:

load *, start + iterno() - 1 as KEY, iterno() as IterNo, rowno() as RowNo while start + iterno() - 1 <= end;
load * inline [
start, end, commission rate
0 , 4, 1%
5, 10, 2%
11,15, 3%
];

- Marcus