Guidance on Proper Beast Mode Syntax for Nested Cases

arzconci
arzconci Member
edited April 2023 in Beast Mode

Hi everyone. I am trying to build-up a series of "cases" that will identify if our shipment is either "Hit", "Miss", or "Risk" in that order. "N/A" if shipment does not satisfy any of the 3.

We have created a logic for each identifier. It is showing as validated but we are not getting the expected results with a lot going to "N/A"

I shared the current beastmode below. Is there anything wrong how I created the logic? Please let me know if further clarification is needed. Thank you so much.

--------------------

Case when`actual arrival at pod date`is not NULL

   AND

   (DATEDIFF(`actual departure from pod date`,`actual arrival at pod date`) < = `Port free time`)

   THEN 'HIT'

    

   when `actual arrival at pod date` is not NULL

   AND

   (DATEDIFF(`actual departure from pod date`,`actual arrival at pod date` ) > `Port free time`)

   OR

   `actual arrival at pod date` is not NULL

   AND

    (DATEDIFF(`current date`,`actual arrival at pod date` ) > `Port free time`)

   THEN 'MISS'

    

   when `actual arrival at pod date` is not Null

   AND

   `actual departure from pod date` is NULL   

   AND

   (DATEDIFF(`current date`,`actual arrival at pod date`) > = 3)

   AND

   (DATEDIFF(`current date`,`actual arrival at pod date` ) < `Port free time`)

   Then 'Risk'

   

 Else 'N/A'

   END

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @arzconci Without seeing your actual data and results, the first thing that stands out to me is the OR in your criteria for 'MISS'. When you are using OR operators, be sure to use parenthesis to group relevant criteria. For example, if both of the statements before and after the OR must be true, then it should look like this:

       when ( `actual arrival at pod date` is not NULL

   AND

   (DATEDIFF(`actual departure from pod date`,`actual arrival at pod date` ) > `Port free time`) )

   OR

   ( `actual arrival at pod date` is not NULL

   AND

    (DATEDIFF(`current date`,`actual arrival at pod date` ) > `Port free time`) )

   THEN 'MISS'

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @arzconci Without seeing your actual data and results, the first thing that stands out to me is the OR in your criteria for 'MISS'. When you are using OR operators, be sure to use parenthesis to group relevant criteria. For example, if both of the statements before and after the OR must be true, then it should look like this:

       when ( `actual arrival at pod date` is not NULL

   AND

   (DATEDIFF(`actual departure from pod date`,`actual arrival at pod date` ) > `Port free time`) )

   OR

   ( `actual arrival at pod date` is not NULL

   AND

    (DATEDIFF(`current date`,`actual arrival at pod date` ) > `Port free time`) )

   THEN 'MISS'
  • arzconci
    arzconci Member

    @MichelleH Thank you for the advise Michelle! Noted and will update my DOMO logic on it. Sorry, basic question, how do you paste Beast Mode syntax in that format?

  • MichelleH
    MichelleH Coach

    Hi @arzconci if you click the paragraph icon on the left side of the text editor, it opens more text format options:

    image.png
    image.png


  • GrantSmith
    GrantSmith Coach

    Alternatively you can use can type ``` and a code block will pop up for you.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • arzconci
    arzconci Member

    Thanks Michelle and Grant.

    Anyways, going back to the original topic, for the "Miss", @MichelleH, there are actually only 2 groups of conditions. I need to have output as Miss if either lines 1-5 or 9-13 is satisfied. So based on your advice, I can also just group them via parenthesis? Sample below

       ( when `actual arrival at pod date` is not NULL

   AND

   (DATEDIFF(`actual departure from pod date`,`actual arrival at pod date` ) > `Port free time`) )

   OR

   ( `actual arrival at pod date` is not NULL

   AND

    (DATEDIFF(`current date`,`actual arrival at pod date` ) > `Port free time`) )

   THEN 'MISS'


  • MichelleH
    MichelleH Coach

    @arzconci Yes, what you posted should work if you move the first open parenthesis to after the word "when".

Categories