Calculated Column Errors - count conditions when like this but not like that

Jacinta

I would like to create a column in my dataset with a count of distinct session_id's where column event_name is 'search' but does not have an event_name called 'select_item' present as well. Hoping a beast mode will work because this is a gigantic dataset that takes hours to run. let me know if you think adding directly to the big query query would be better however.

Here is the beast mode I tried and its not liking it.

COUNT(DISTINCT session_id CASE WHEN event_name NOT LIKE '%select_item%' and event_name LIKE '%search%' then 'Refined' END )

thanks!

ColemenWilson

Hmm it is validating for me when I replicate it in my instance. Can you share a screenshot of your beastmode?

ColemenWilson

Should work if you remove the then 'Refined' part at the end and move the session_id . See below:

COUNT(DISTINCT CASE WHEN event_name NOT LIKE '%select_item%' and event_name LIKE '%search%' THEN session_id  END )

Keep in mind that you are using wildcard characters around select_item and search which will return values that contain characters on either side of that string search.

Jacinta

thanks so much for the response. I tried the formula provided and it says there's a calculation error :(

ColemenWilson

Hmm it is validating for me when I replicate it in my instance. Can you share a screenshot of your beastmode?