community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Create a range field base on other column values

Asteroid

I am working with credit score data and need to break out the scores in 20pts increments to do some summary statistics. 

I have 43K rows of data all which have a credit score ranging from 300-880.

 

What I would like to do is add a field that says what range the score is in so I can do a group by....

RANGEACCT #CREDIT SCORECURRENT BALRATE
640-6590123652219658.01
740-7590456749476586.45
320-3390789326116979.99

 

Is there a way to easily add a range field without inputting a file with the 30 different range values, and then a extremely long IF THEN functions that calls the score to the range?

Magnetar
Magnetar

For this particular example I would use math and a formula like the following:

TOSTRING(FLOOR([CREDIT SCORE]/20)*20)+'-'+TOSTRING(CEIL([CREDIT SCORE]/20)*20-1)

 

This will display the range numbers you want by taking the lower and upper bounds of each range per score, and won't require a lookup or complex formula. 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @IJH34 

 

I'd do this way:

GenerateRange.PNG

 

- Generate Rows to generate your range numbers with intervals of 20

- Use Multi-Row Formula to create the end point of the Range

- Filter tool to leave out the last value (880)

- Create the Range string with Range Values

- Use Append Tool to append all ranges and the filter by a condition to match the range (value between start point and end point) and bring desired values

 

Of course it depends of the size of your dataset. Appending sometimes might not be the best way to go, so other alternatives should be considered.

 

WF attached.

 

Cheers,

Asteroid

@Claje I like this idea however, my ranges seems a bit funky. I have the 20pt intervals but I also have 1pt intervals following. What is causing this?

2019-06-10_11-40-13.jpg

Labels