Data match within range

[Viktor1]

Hi,

I've got two tables as per below. I need to assign Output in table two based on the Category and Value falling with ranges.

There are multiple categories. Ranges are different per category. Ranges will change over time.

So ideally this would be dynamic solution. Any ideas?

Thanks,

Viktor

Category	Lower range	Upper range	Output
ABC	0	5	1
ABC	5.0001	15	2
ABC	15.0001	30	3
ABC	30.0001	50	4
ABC	50.0001	100	5
DEF	0	4	1
DEF	4.0001	12	2
DEF	12.0001	24	3
DEF	24.0001	48	4
DEF	48.0001	100	5

Category	Value	Output
ABC	3	?
ABC	13	?
ABC	23	?
ABC	33	?
ABC	43	?
DEF	2	?
DEF	12	?
DEF	22	?
DEF	32	?
DEF	42	?

[mborriero · Accepted solution]

Hi @Viktor1, I would use a join by category then a filter to exclude unwanted rows. You can change your input whenever you need.

[Viktor1]

Thanks!

[Oskih]

Hi!

I have a similar problem but I only have "ABC" data.

Problem is that I have 20 million rows and and 300 "range criterias" instead of 5 which it is in this example. This join function creates 300 new rows for each row. That means I will have 6 bn rows which I will then apply the filter on. This will take to much time to do in Alteryx.

Is there a way to do this that does not take that much processing power?

Thanks!