Identify if value exists in field to then classify data

afieweger

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.

1. evaluate the data set to find where at least 1 participant joined by "Video"
2. and then establish a field that indicates "Video Enabled" or such.
3. 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!

GrantSmith

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.

nmizzell

Could you send an example dataset with the desired output?

GrantSmith

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.