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 CompanyCompany financial ratioA 0.05B0.14C0.15D0.16E0.24 2) Global financial ratioRank classification0.0510.120.230.34 Output table: CompanyRank classificationA1B2C3D3E3 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!

Match a number that falls within an interval from another table

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

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!

Accepted answers

RobertOdera

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!

For_sarasustelosantos.yxmd

sarasustelosantos

@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.

example.yxmc

All comments

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.

IntervalRatio.yxzp

RobertOdera

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!

For_sarasustelosantos.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703