Identify if value exists in field to then classify data

afieweger
afieweger Member
in Beast Mode

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!

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    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!**

Answers

  • nmizzell
    nmizzell Contributor
    edited February 2024

    Could you send an example dataset with the desired output?

  • GrantSmith
    GrantSmith Coach
    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!**

Categories