Adding condition for Length to differentiate shorts and long video

[dinecc]

Hi all, 

Im creating a Looker Dashboard. I would to create a conditional field where anything less than 1 minute is Shorts else long form video. I wrote this formula, however, all the videos are showing up as long form video. Can anyone please assist here? 

CASE
WHEN REGEXP_CONTAINS(Video Length, "^00:00:") THEN 'Shorts'
ELSE 'Long video'
END

[rudy-looker]

Hi @dinecc 

Could you give an example of a data value in your "Video Length" column?

Assuming it's just string e.g. "00:00:34" or "00:05:42", when I tried using BQ:

SELECT video_length, CASE WHEN REGEXP_CONTAINS(video_length, "^00:00:") THEN "SHORT" ELSE "LONG" END as length 
FROM (SELECT ['00:00:02',"00:03:24"] as len), UNNEST(len) as video_length;

will give you the correct SHORT/LONG as result, so your formula does seem to be correct. Error might be elsewhere like column reference, actual data values, etc.