Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Strange interval match issue

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.

 

 

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

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!

View solution in original post

10 Replies
Highlighted
Partner
Partner

Hi,

 

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

ex Round(Field,0.0001)

Highlighted

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

Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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

Highlighted
Partner
Partner

Because using range like that

0 5

5.0001 10

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

 

Highlighted
Contributor III
Contributor III

Good point! I should improve accordingly.

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

Highlighted
Partner
Partner

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;

 

Highlighted
Contributor III
Contributor III

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!

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

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