Match a number that falls within an interval from another table

[sarasustelosantos]

Hi there! I am trying to finding out how to make different numbers hit a specific interval given from another table. The numbers should be rounded to the closest number and then a rank is attributed. My original dataset has 300k companies and 20 financial ratios.

We have two input tables that look like this:

1) Financial ratios for each company

Company	Company financial ratio
A	0.05
B	0.14
C	0.15
D	0.16
E	0.24

2)

Global financial ratio	Rank classification
0.05	1
0.1	2
0.2	3
0.3	4

Output table:

Company	Rank classification
A	1
B	2
C	3
D	3
E	3

So the goal is to match the financial ratio for each company with a rank classification, according to the interval where it belongs. The financial ratio is rounded to the closest value.

Thank you in advance!

[grazitti_sapna]

Hi @sarasustelosantos , you can use formula tool and  write this syntax given below

Round([Company financial ratio],0.1)

and then join table A and B.

Please refer to the screenshot for the desired output.

[sarasustelosantos]

Hello @grazitti_sapna , thank you for your fast reply! The workflow presented works with the data I gave in the example, however, it's a bit more complicated than that and I though Round was enough, but it's not. I really need to find the closest value. After joining the two input tables I want to find out what should be the rank classification of the company and this would be the output for company A:

Company	Financial ratio	Global financial ratio	Rank classification (global)	Rank classification (company)
A	0.14388	0.13152	1	null
A	0.14388	0.13375	2	null
A	0.14388	0.13475	3	null
A	0.14388	0.13806	4	null
A	0.14388	0.14021	5	null
A	0.14388	0.14652	6	6
A	0.14388	0.15154	7	null

In order to get to the last column, I think the best way is to find the minimum value between the difference of those two columns (Financial ratio and Global financial ratio)... how can I do this in Alteryx?

[grazitti_sapna]

---

@sarasustelosantos wrote:

Hello @grazitti_sapna , thank you for your fast reply! The workflow presented works with the data I gave in the example, however, it's a bit more complicated than that and I though Round was enough, but it's not. I really need to find the closest value. After joining the two input tables I want to find out what should be the rank classification of the company and this would be the output for company A:

Company	Financial ratio	Global financial ratio	Rank classification (global)	Rank classification (company)
A	0.14388	0.13152	1	null
A	0.14388	0.13375	2	null
A	0.14388	0.13475	3	null
A	0.14388	0.13806	4	null
A	0.14388	0.14021	5	null
A	0.14388	0.14652	6	6
A	0.14388	0.15154	7	null

In order to get to the last column, I think the best way is to find the minimum value between the difference of those two columns (Financial ratio and Global financial ratio)... how can I do this in Alteryx?

---

@sarasustelosantos , could you tell me how did you find out the rank classification for the company as the whole column is null but only 2nd last row has a value 6 so a bit curious how did you find it?Also if we try to find the difference between the financial and global ratio the numbers would result in negatives.Anyways I have given a try as per my understanding. Please let me know if it works for you.

[RobertOdera · Accepted solution]

Hi, @sarasustelosantos 

Please mark this as an acceptable solution, and Like, if this works for you.

Solution (since only one company in sample file)

Workflow

The workflow is attached.

Cheers!

[sarasustelosantos · Accepted solution]

@grazitti_sapna and @RobertOdera I ended up finding what I needed and built a Macro. Thank you so much for your help anyway! Attached is macro for anyone that needs it.