Guidance on Proper Beast Mode Syntax for Nested Cases

arzconci

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

MichelleH

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

MichelleH

@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

@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

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

GrantSmith

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

arzconci

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

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