Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Ideas

Share your Designer product ideas - we're listening!

Add CASE to conditional functions supported outside of databases

SQL and Tableau both support a CASE function with the syntax


  WHEN Condition 1 THEN Result 1

  WHEN Condition 2 THEN Result 2 ...

  WHEN Condition N THEN Result N

ELSE Default Result



I'm thrilled that I can now use this with the In-DB tools, but I'd love to have this available in the regular function tools so I can stop nesting my IFs!



In an Alteryx function one option somewhat equivalent to this is a Switch




But the downside is that rather than full flexibility on your conditions, you are always comparing against the Value.  So each condition is Value==Case1; Value==Case2... etc.


And you say you are nesting your IFs.  Did you know about ELSEIF?  Which can give a very similar syntax to the SQL CASE


IF Condition 1 THEN Result 1 

ELSEIF Condition 2 THEN Result 2...

ELSEIF Condition N THEN Result N

ELSE Default Result




Wait that works now? Yay! In 9.5 I always got error mesages when I tried to use more than one ELSEIF without nesting, and I assumed based on the syntax description that the error was because you were only allowed one ELSEIF. (It was probably some other user error though.)


Can you change the description for this to "IF c THEN t ELSEIF c2 THEN t2 ... ELSEIF cN THEN tN ELSE f ENDIF" or something else that conveys the joys of unlimited ELSEIFs?


Ran across this post trying to tackle a something similar.  It looks like (in v10.6~) you can use a switch function like you would case statement by setting the value to boolean true.  In my case, I was using the Contains function that returned -1 for true (which is the weirdest boolean "true" value I've ever seen):

Contains([firstname], "jim"),"James",
Contains([firstname], "kim"),"Kimberly"

Elseif can work to define the logic needed to complete a basic data munging task, but I strongly believe that there needs to be support for a case statement in Alteryx. Consider this example:

Mapping project names to a large dataset for reporting on a programs financials where there are 50 small projects to map to. It is possible using ELSEIF, but understanding the logic is very hard with multiple ELSE IF's when you have lots of values to lookup/map to.

Project Name   Code

Project A          4-675

Project B          7-765

Project N          x-xxx