Identify if value exists in field to then classify data
Hey community. I've got a data set that is not optimal for what I'm trying to do. My optimal data set is sitting in a backlog and I'm looking to get some preliminary answers while i wait - so frankly, looking for a hack.
I have a data set that has all of the participants associated with all of the events that we track. So this set has the Event UNID for each participant row with specific meta data about the partiicpant-to-event relationship.
Those participants can join the event by live Video or Phone and this value is available on each of the participant data rows. Importantly, some events ONLY have phone.
Problem: I'd like to be able to identify all Events that support Video and then use that to indicate the breakdown of how participants joined for those events that support video.
- evaluate the data set to find where at least 1 participant joined by "Video"
- and then establish a field that indicates "Video Enabled" or such.
- so that I can then create cards off of this data set to filter on this new value to get only those events with video enabled.
I don't have access to change ETL flows. Any way to do this in beast mode?
Thank you!
Best Answer
-
CASE WHEN MAX(`Attendance Type`) OVER (PARTITION BY `Event UNID`) = 'Video' THEN 'Yes' ELSE 'No' END
You can use a window function to return a value across an entire bucket (in your case the event). MAX will return Video if a video record exists (it comes alphabetically after Phone). Wrapping in CASE statement to return either Yes or No for your flag then.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers
-
Could you send an example dataset with the desired output?
0 -
CASE WHEN MAX(`Attendance Type`) OVER (PARTITION BY `Event UNID`) = 'Video' THEN 'Yes' ELSE 'No' END
You can use a window function to return a value across an entire bucket (in your case the event). MAX will return Video if a video record exists (it comes alphabetically after Phone). Wrapping in CASE statement to return either Yes or No for your flag then.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 620 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 742 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive