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

Jacinta
Jacinta Member
in Beast Mode

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!

Tagged:

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

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

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited July 2023

    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.

    If I solved your problem, please select "yes" above

  • Jacinta
    Jacinta Member

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

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

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

    If I solved your problem, please select "yes" above

Categories