Error when creating a case statement for a data flow column

user026529

What am I doing incorrect?

It gives me an error while using the case statement.

MarkSnodgrass

I think all you need to do is have a space between CASE and your fieldname 'WPS Score'. It looks like they are together so Domo can't read it correctly.

user026529

I tried again with changed syntax, but again same error.

MarkSnodgrass

Actually, after trying a similar statements, you can't do a < or IN statement like that. You need to write it like this:

(CASE WHEN `wps score` < 6 THEN 'Detractor'
WHEN `wps score` < 8 THEN 'Passive'
ELSE 'Promoter'
END)

user026529

But then what happens for values between 6 to 8, because in 2nd statement anything under 8 will flag as passive, but I want them as detractors and only between 6-8 as passive

MarkSnodgrass

Case statements exit after finding a match so you don't need to do a between or in statement. If, for example, your score is 7, it will evaluate the first line and since it is not less than 6, it will go to next when statement and see if it evaluates as true. It would then exit out after evaluating a true being less than 8 and be labeled as passive. If the value is 4, it would exit after the first statement and be labeled as a detractor.

The key is just to stack your when statements in order, from lowest to highest in this case, for the evaluations to work. If you changed the order, and just put them in "randomly" you would get undesirable results.

user026529

So Just for understanding, why can I not use IN statement in this syntax?

because I saw a similar application of IN statement in documentation here

https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Transforming_Data_Using_Beast_Mode/02Beast_Mode_Functions_Reference_Guide

MarkSnodgrass

If you wanted to use an IN operator, your WHEN line would need to look like this:

WHEN `wps score` IN (6,7) THEN 'Passive'

IN clauses hold a list of values. You can't use greater than or less than operators within the list.

You can technically use whatever is most readable to you and others that would need to understand the beast mode function.

user026529

So basically its because "<" or ">" operator are not supported for IN statement?

Also I noticed that you had a parenthesis before CASE and after the END. does case statement needs to be in parenthesis?

MarkSnodgrass

That is correct about the "<" and ">" in the IN statement.

The parentheses are optional around the CASE statement. Domo automatically adds them when you start typing in the formula editor and use the auto-complete option.

MarkSnodgrass

@user026529 if you can accept answer and/or like any of the above responses that will help others in the community as they search for similar questions.

Hoped all of this helped!

jaeW_at_Onyx

@MarkSnodgrass kudos for the mega patient answers! I love that.

@user026529 , altthough Beast Modes ARE NOT executed in MySQL they do generally follow MySQL syntax. If you're encountering a Syntax error, just google your function + MySQL and it will generally accurately describe the syntax you're supposed to use.

There are some small exceptions... but there you go.

No you don't have to wrap a CASE inside of parenthesis. Assume that each Beast Mode must be a syntactically correct argument in a SELECT clause.

I.E.

SELECT

CASE WHEN ... END

user026529

Thanks. I realized that my syntax error was because I was using IN with WHEN. The moment I removed IN the error went away, but the way DOMO described the error was kind of misleading and not helpful. It should have created an error on IN statement.

jaeW_at_Onyx

@user026529 , the IN argument works fine in a CASE statement in Magic 2.0 formula tile. just tested. Use MySQL syntax.

user026529

Then what was the meaning of the error as in the screenshot?

MarkSnodgrass

@user026529 The error message could use some improvement, but it doesn't like the when <6 statement and it doesn't like your arguments in the In statement.

You need to write it how I suggested earlier, which was:

(CASE WHEN `wps score` < 6 THEN 'Detractor'
WHEN `wps score` < 8 THEN 'Passive'
ELSE 'Promoter'
END)