Add CASE to conditional functions supported outside of databases

[ksklar]

SQL and Tableau both support a CASE function with the syntax

CASE

  WHEN Condition 1 THEN Result 1

  WHEN Condition 2 THEN Result 2 ...

  WHEN Condition N THEN Result N

ELSE Default Result

END

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!

[AdamR_AYX]

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

Switch(Value,Default,Case1,Result1,...,CaseN,ResultN)

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

ENDIF

[ksklar]

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?

[jgraves]

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):

Switch(-1,Null(),
Contains([firstname], "jim"),"James",
Contains([firstname], "kim"),"Kimberly"
)

[pgensler]

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

[Community_Admin]

The status of this idea has been changed to 'Inactive'. This status indicates that:

1. The idea has not had activity in the form of likes or comments in over a year.

2. The idea has not reached ten likes.

3. The idea is still in the 'New Idea' status. 

However, this doesn't mean your idea won't be implemented! The Community can still like and comment on this idea. With enough renewed interest, this idea can be brought back into the 'New Idea' status. 

Thank you for contributing to the Alteryx Community and the Alteryx Product Idea Boards!