Multiple Nested Case Statements Help

Reply
Highlighted
Black Belt

Re: Multiple Nested Case Statements Help

@Valiant is on the right track.

 

The way that a case statement evaluates is in the sequence of the when statements.

 

In your example, 

CASE 
    WHEN `Case Owner Manager` IN ('a', 'b,'c')
    THEN 'Tier 1'
CASE 
    WHEN `Case Owner Manager` IN ('d','e','f','g')
    Then 'Tier 2'
CASE 
    WHEN `Case Owner Manager` IN ('h', 'i')
    Then 'Strategic'
ELSE 'Others'
END

You are using too many CASE statements.  Also, if you do run into a situation where you need to nest a case statement, each case statement must have at least one "when" statement, one "then" statement, and an "END" statement.  In your code, you have three case statements but you only "end" the last one.

 

However, this is not a situation that requires nesting.

CASE WHEN `Case Owner Manager` IN ('a', 'b,'c')
           THEN 'Tier 1'
           WHEN `Case Owner Manager` IN ('d','e','f','g')
           Then 'Tier 2'
           WHEN `Case Owner Manager` IN ('h', 'i')
           Then 'Strategic'
           ELSE 'Others'
END

Looking at @Valiant 's code, this is how Domo will evaluate it... (there is a typo in the first line) It should be "CASE WHEN `Case Owner Manager` IN ('a', 'b', 'c')... he put the comma in the wrong spot for b

 

The first when statement - If the field `Case Owner Manager` is equal to 'a', 'b', or 'c' then it will output `Tier 1`

 

If the first statement is not satisfied, it will then look to evaluate the second when statement - 

if the field `Case Owner Manager` is equal to 'd','e', 'f', 'g' then it will output 'Tier 2'

 

This is still part of the same case statement.  If the line of code being evaluated meets the second when condition, then the remaining when conditions are not evaluated at all.  This is why, for performance issues, you should list the most commonly occuring when conditions at the start of a long case statement.

 

Hope this was helpful.  Let us know if you are still having issues with this case statement. 


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Highlighted
Yellow Belt

Re: Multiple Nested Case Statements Help

Thank you. It worked.

 

I have one more question can we use two different conditions for the same output.

Example:

CASE WHEN `Case Owner Manager` IN ('a', 'b,'c')
THEN 'Tier 1'
WHEN `Case Owner Manager` IN ('d','e','f','g')
Then 'Tier 2'
WHEN `Case Owner Manager` IN ('h', 'i')
Then 'Strategic'
ELSE 'Others'
END

 

also 'Case Owner' as one more condition here?

Announcements
Win free lodging at Domoapalooza! We want you to share you favorite Domo tips and tricks. Click here for more details!