Formula in Alteryx Tool

[duttaabhishek7]

Hi I have the below data set :

1. Denmark 90
2. Finland   77
3. New-zealand 63
4. Norway 54
5  Singapore 23

Now I want to assign Score according to the Countrywise score as per below table in a new column. Please suggest me the best way to do it please. I am very new in Alteryx. 

Score Range                              Score Assigned

>= 90                                                     1

80-89                                                      2

70-79                                                     3

60-69                                                      4

50-59                                                      5

40-49                                                      6

30-39                                                      7

25-29                                                       8

20-24                                                       9

0-19                                                         10

[ShankerV · Accepted solution]

Hi @duttaabhishek7 

As per the attached sample, there are rows containing country names separated with space and ending with numbers.

Step 1: Regex tool

Step 2: Find and Replace tool

Many thanks

Shanker V

[ShankerV · Accepted solution]

Hi @duttaabhishek7 

Please find the details steps below.

Step 1: Regex tool

Column to Parse: Column name of Countries with numbers

Regular expression: (.+)\s(\d+)

Output Method: Parse

Step 2: Find and Replace tool

Find Within Field: RegExOut2

Find Value: Score Range

Append Fields to Record, Select Score Assigned

Important note: As the country scores are >= 90 = 1

I request to create an excel sheet with below.

Column A: Score Range : starting from 0 to 100

Column B: Score Assigned: 0-19 mark as 1, 20-23 as 2 and so on.

Then use select tool to convert the numeric datatype of columns Score Range and Score Assigned to V-WString. (As interget datatype will not be supported in Find and Replace tool)

Many thanks

Shanker V

[Raj · Accepted solution]

@duttaabhishek7 one way of doing this.
hope this helps.