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

Salmas · 2021-11-30T16:22:26+00:00

There was an error rendering this rich post.

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

Find more posts tagged with

Accepted answers

MarkSnodgrass

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.

All comments

MarkSnodgrass

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.

GrantSmith

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.