cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
FRIDAY Deadline | Submit your Alteryx Use Case by August 17th to qualify for this round of Analytics Excellence Awards presented at Inspire Europe 2018! Learn more.

Change name based on last value in category name

SOLVED
Asteroid

If a Last Name ends with a S  I want to change the Category from whatever it is to something else like cookies.

 

I assume I need to use a Right function, but I don't know how to mix this with an if statement.

 

Any thoughts?

Quasar

Hi,


Here's a quick example of exactly what you're looking to do:

IF RIGHT([Last Name],1) = "S" THEN
"Cookies"
ELSE
[Category]
ENDIF

Notably, you can also use ENDSWITH() To accomplish the same thing and avoid calling out string length

 

IF ENDSWITH([Last Name],"S") THEN
"Cookies"
ELSE
[Category]
ENDIF

I wanted to point out that if the case of the letter "S" is important, the solutions from @Claje handle case differently.

 

In the first example, the RIGHT() syntax is case-sensitive so the replacement will only work with an uppercase "S". To make the RIGHT() syntax example case-insensitive, you could:

 

1)

IF
RIGHT([Last Name]),1) = "S" OR RIGHT([Last Name),1) = "s"
THEN
"Cookies"
ELSE
[Category]
ENDIF

2)

IF
RIGHT(UPPERCASE([Last Name]),1) = "S"
THEN
"Cookies"
ELSE
[Category]
ENDIF

In the second example, the ENDSWITH() syntax defaults to being case-insensitive, therefore, as written both 'S' and 's' would become "Cookies". If case is important, you could add the third parameter to the ENDSWITH() function:

 

IF
ENDSWITH([Last Name],"S",0) THEN "Cookies" ELSE [Category] ENDIF