AND function is not working in the case statement for a single column values
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'
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
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.**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
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!**2
- 7.7K All Categories
- 12 Connect
- 925 Connectors
- 247 Workbench
- 441 Transform
- 1.7K Magic ETL
- 61 SQL DataFlows
- 456 Datasets
- 88 Visualize
- 222 Beast Mode
- 2.1K Charting
- 8 Variables
- 35 Cards, Dashboards, Stories
- 5 Automate
- 349 APIs & Domo Developer
- 85 Apps
- 17 Predict
- 3 Jupyter Workspaces
- 14 R & Python Tiles
- 242 Distribute
- 60 Domo Everywhere
- 241 Scheduled Reports
- 18 Manage
- 39 Governance & Security
- 46 Product Ideas
- 1.1K Ideas Exchange
- 6 Community Forums
- 19 Getting Started
- 6 Community Member Introductions
- 50 Community News
- 18 Event Recordings
- 577 日本支部