Error when creating a case statement for a data flow column

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.

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)

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.

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.

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 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 , the IN argument works fine in a CASE statement in Magic 2.0 formula tile. just tested. Use MySQL syntax.

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