Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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!
Hi,
have you already tried to round the value before using interval match?
ex Round(Field,0.0001)
Would you be able to share a sample where we can see this? May be we can spot the cases by looking?
I didn't. Do you mean to round given the profit%? What difference it will make?
I guess not possible because it is in internal network, and file is very big, also sensitive.
Because using range like that
0 5
5.0001 10
If you have a value like 5.000007, it will be null
Good point! I should improve accordingly.
However, I checked the value, it is exactly in between the range.
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...
RangeAssignments:
Load
FieldValue('Your Field',Recno()) as [Your Field]
Autogenerate FeildValueCount('Your Field');
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
];
Inner Join(RangeAssignments)
Load
[Your Field],
BeginRange,
EndRange
Resident RangeAssignments
Where [Your Field] > BeginRange and [Your Field] <= EndRange;
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!
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