Using Conditional Statements Add Character for Sorting Puposes

[bryan_ram2613]

Hello all,

I have had an issues simply adding characters based on conditions. The purpose is to sort in an external visual tool. Example below.

Variable Rage

Extremely Low (Below 0.54)

Low (0.54 to 1.35)

Below Average (1.35 to 3.35)

Average (3.35 to 8.4)

Above Average (8.4 to 21)

High (21 to 53)

Extremely High (Above 53)

I am trying to get to what is below and running into issues with the formula especially when trying to isolate "Low" for example. Also, I have many other variations of the actually numbers in parentheses so I have to rely on the text ranges in order to replace because those are constant throughout. 

Variable Rage

1. Extremely Low (Below 0.54)

2. Low (0.54 to 1.35)

3. Below Average (1.35 to 3.35)

4. Average (3.35 to 8.4)

5. Above Average (8.4 to 21)

6. High (21 to 53)

7. Extremely High (Above 53)

Can someone assist with the formula??

Best

[JoBen]

Hi @bryan_ram2613, are you basically trying to sort your ranges based on the numbers in the parentheses? 

[bryan_ram2613]

@JoBen  Yes but in another vis tool outside of Alteryx. I was hoping to just handle it in Alteryx and not have to do a bunch of changes later. 

[danilang · Accepted solution]

hi @bryan_ram2613 

Use the Startswith function to find your various cases

if startswith([Variable Rage],"Extremely Low") then 
	"1. " + [Variable rage]
elseif startswith([Variable Rage],"Low") then 
	"2. " + [Variable rage]
elseif startswith([Variable Rage],"Below Average") then 
	"3. " + [Variable rage]
elseif startswith([Variable Rage],"Average") then 
	"4. " + [Variable rage]
elseif startswith([Variable Rage],"Above Average") then 
	"5. " + [Variable rage]
elseif startswith([Variable Rage],"High") then 
	"6. " + [Variable rage]
else
	"7. " + [Variable rage]
endif

Dan