cancel
Showing results for 
Search instead for 
Did you mean: 

Add CASE to conditional functions supported outside of databases

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!

 

4 Comments
Alteryx
Alteryx

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
Meteor

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

 Conditional syntax

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
Atom

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"
)
petergensler
Meteoroid

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