AND function is not working in the case statement for a single column values

Hi ,

I'm working with the Domo Governance dataflow, and I wanted to create a card which shows a message based on the successful update.

I have 4 dataflow A,B,C,D. I wanted to see a message 'successful', if all the 4 dataflow has ran successfully today. Else it should should show a message 'Pending'.

Here is my beast mode:

Case when Name = 'A' and Name = 'B' and Name = 'C' and Name = 'D' and

date(last updated time) = Date(Curdate()) and Last Execution state = 'Success' then 'Success'

Else 'Pending'

Now, all the dataflow has ran successfully, but the message is showing as Pending, which is wrong. Is there any issue in my beast mode.

Looking forward for a solution.

Thanks in advance

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷
    Answer ✓

    Your beast mode will never return Success as currently constructed because it evaluates the data one row at a time. Therefore, Name will never equal 4 different things in one row.

    Depending on what card type you want to use, you might try doing the following:

    Add Name to your filters and select your 4 dataflows

    Add Last Execution State to your filter and choose Success

    Set the date range filter to today and make sure it is using Last Updated Time as the date field

    Create a beast mode with the following:

    CASE WHEN COUNT(Last Execution State) = 4 THEN 'Success' ELSE 'Pending' END
    

    Drag this beast mode into your card

    The count will only equal 4 when all 4 of those dataflows have a success state for the current date. This should accomplish what you were trying to do.

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • GrantSmith
    GrantSmith Indiana 🥷

    Hi @Salmas

    Your beast mode isn't working because you're attempting to compare a single value to 4 multiple values at the same time. If Name is A it will never be B, C or D so your result will always be 'Pending'

    If you're doing this across your entire dataset you could do something like this to check them all individually and aggregate the data and compare them together (pseudo-code, I haven't exactly tested this):

    CASE WHEN (MAX(CASE WHEN `Name` = 'A' AND date(last updated time) = Date(Curdate()) and Last Execution state = 'Success' THEN 1000 ELSE 0 END) +
    MAX(CASE WHEN `Name` = 'B' AND date(last updated time) = Date(Curdate()) and Last Execution state = 'Success' THEN 100 ELSE 0 END) + 
    MAX(CASE WHEN `Name` = 'C' AND date(last updated time) = Date(Curdate()) and Last Execution state = 'Success' THEN 10 ELSE 0 END) +
    MAX(CASE WHEN `Name` = 'D' AND date(last updated time) = Date(Curdate()) and Last Execution state = 'Success' THEN 1 ELSE 0 END)) = 1111 THEN 'Success' ELSE 'Pending' END
    

    I'm simply doing "binary" addition such that I know A was successful if the thousands is 1, B is successful if the hundreds is 1, C = tens is 1 and D = ones is 1. You could also just do 1 for all the values and compare to 4. I'm also taking the MAX to avoid duplicate entries.

    Again this is also assuming you're not trying to do this on a row level basis but aggregating across the entire dataset to get a single textual representation of your status.



    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**