topic Re: Strange interval match issue in QlikView

Strange interval match issue

achates2008 — Sat, 16 Nov 2024 02:31:12 GMT

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.

Re: Strange interval match issue

StarinieriG — Tue, 10 Sep 2019 13:26:19 GMT

Hi,

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

ex Round(Field,0.0001)

Re: Strange interval match issue

sunny_talwar — Tue, 10 Sep 2019 13:26:43 GMT

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

Re: Strange interval match issue

achates2008 — Tue, 10 Sep 2019 13:33:56 GMT

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

Re: Strange interval match issue

achates2008 — Tue, 10 Sep 2019 13:34:58 GMT

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

Re: Strange interval match issue

StarinieriG — Tue, 10 Sep 2019 13:39:35 GMT

Because using range like that

0 5

5.0001 10

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

Re: Strange interval match issue

achates2008 — Tue, 10 Sep 2019 13:43:05 GMT

Good point! I should improve accordingly.

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

Re: Strange interval match issue

chriscammers — Tue, 10 Sep 2019 14:11:53 GMT

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;

Re: Strange interval match issue

achates2008 — Tue, 10 Sep 2019 14:32:54 GMT

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!

Re: Strange interval match issue

marcus_sommer — Wed, 11 Sep 2019 14:22:06 GMT

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

Re: Strange interval match issue

Brett_Bleess — Mon, 23 Sep 2019 16:34:37 GMT

Just going to leave a couple of links here as well that may come in handy for others that run across this post:

Design Blog post on IntervalMatch:

https://community.qlik.com/t5/Qlik-Design-Blog/IntervalMatch/ba-p/1464547

Design Blog post on rounding issues:

https://community.qlik.com/t5/Qlik-Design-Blog/Rounding-Errors/ba-p/1468808

Hopefully these may help others that run across this one. Achates, I am also going to mark your last post as the solution on this one as well, just FYI.

Regards,
Brett