Case statement clarification

This seems pretty straightfoward, but I wasn't able to find a definitive answer searching Domo or Google. We're hoping to clarify whether the beast mode formula (case statement using "OR") will return a total count of five (5), i.e. count each row one time or eleven (11), i.e. instead counting EACH instance of a condition being met individually. An example of the beast mode & data being used is below.

 

Thanks in advance for any clarification provided...

 

Beast Mode

(CASE
WHEN (`Record.Name` LIKE '%Team1%'
OR `Owner.Title` LIKE '%Team1%'
OR `Owner.Name` = 'Loss Prevention - Direct'
OR `Owner.Name` = 'Loss Prevention - Indirect'
OR `Owner.Name` = 'Claim Follow-up - Direct'
OR `Owner.Name` = 'Claim Follow-up - Indirect'
OR `Owner.Name` LIKE '%Team1%') THEN 1
ELSE 0
END) END)

 

Data:
Screen Shot 2018-01-30 at 1.54.50 PM.png

 

 

 

 

 

Best Answer

  • Valiant
    Valiant Coach
    Answer ✓

    In your example a count on the beastmode would return 5. Once a value is found in a multiple OR statement like that, it immediately dumps out to the THEN and ENDS. It wouldn't get caught multiple times in the same count.

     

    Hope that clarifies. If not, please let me know.

     

    Sincerely,

    ValiantSpur

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

Answers

  • Godzilla
    Godzilla Contributor

    I haven't been able to use OR statements succesfully in beast mode but I don't think it will count it separately because, from my understanding of how the case statement works, as soon as it gets to the first condition that is true, it will stop going through the rest of the statement. For example, if a record meets the `Record.Name` LIKE '%Team1%' condition, it will jump to the Then statement and stop. It will not continue to go through the rest of the case statement.

    Also, i don't think the case statement as you have it will work in a beast mode. I think you'll have to change it to something like:

     

    CASE
    WHEN `Record.Name` LIKE '%Team1%' THEN 1
    WHEN `Owner.Title` LIKE '%Team1%'THEN 1
    WHEN `Owner.Name` = 'Loss Prevention - DirectTHEN 1
    WHEN `Owner.Name` = 'Loss Prevention - IndirectTHEN 1
    WHEN `Owner.Name` = 'Claim Follow-up - DirectTHEN 1
    WHEN `Owner.Name` = 'Claim Follow-up - IndirectTHEN 1
    WHEN `Owner.Name` LIKE '%Team1%' THEN 1
    ELSE 0
    END

    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • Valiant
    Valiant Coach
    Answer ✓

    In your example a count on the beastmode would return 5. Once a value is found in a multiple OR statement like that, it immediately dumps out to the THEN and ENDS. It wouldn't get caught multiple times in the same count.

     

    Hope that clarifies. If not, please let me know.

     

    Sincerely,

    ValiantSpur

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

  • Thanks @Godzilla and @Valiant. So basically the beast mode/case statement will go row by row, and in each row once an OR condition is met it will either count 1 or ignore if none exist, amd then move onto the next row and repeat until all rows of data are checked?

  • @John-Peddle That's correct

  • Thank you both for your assistance! Thought I'd read something previously that stated what your replies indicated but for the life of me couldn't locate that information anywhere in a search, and just wanted to be certain. Have a great day!