Error when creating a case statement for a data flow column

What am I doing incorrect?

It gives me an error while using the case statement.

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟣

    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.

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


  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟣

    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)
    


  • 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
    MarkSnodgrass Portland, Oregon 🟣

    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.

  • 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
    MarkSnodgrass Portland, Oregon 🟣

    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.

  • 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
    MarkSnodgrass Portland, Oregon 🟣

    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
    MarkSnodgrass Portland, Oregon 🟣

    @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
    jaeW_at_Onyx Budapest / Portland, OR 🟣

    @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

  • 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
    jaeW_at_Onyx Budapest / Portland, OR 🟣

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

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

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟣

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


Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!