Data Studio
2K members online now
2K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Problem using Case Statement with REGEXP_EXTRACT

Follower ✭ ✭ ✭
# 1
Follower ✭ ✭ ✭

Hi,

 

My campaign name is in the format => <Country Code>_<Fiscal Year>_<Category Code>_<Brand Code>_<Channel Code>_<Campaign Name>, which I've extracted successfully into 6 parts. 

 

I would like to check if anyone manage to go in a single step to extract say 'Category Code' and convert them via a CASE statement.

 

For example, to extract the Category Code I used the following code:

REGEXP_EXTRACT(Campaign, '^(?:[^_]*_){3}([^_]*)_')

 

Then I created a new field "Category" with the following code:

CASE
 WHEN Category Code = 'ABC' THEN 'Category ABC'
 WHEN Category Code = 'DEF' THEN 'Category DEF'
 ELSE Category Code 
END

 

When I replace the Category Code field reference in the Case statement above,

CASE  WHEN REGEXP_EXTRACT(Campaign, '^(?:[^_]*_){3}([^_]*)_') = 'ABC' THEN 'Search'  WHEN REGEXP_EXTRACT(Campaign, '^(?:[^_]*_){3}([^_]*)_') = 'DEF' THEN 'Social' ELSE REGEXP_EXTRACT(Campaign, '^(?:[^_]*_){3}([^_]*)_') END

 

I got an error message flagging it as invalid formula, and after noticing the CASE only only value in Text, Number and Boolean, I've change the code to the following:

CASE  WHEN REGEXP_EXTRACT(Campaign, '^(?:[^_]*_){3}([^_]*)_') = 'ABC' THEN 'Search'  WHEN REGEXP_EXTRACT(Campaign, '^(?:[^_]*_){3}([^_]*)_') = 'DEF' THEN 'Social'   ELSE 'N/A'  END

 

But I am still getting the same error message saying invalid formula.

 

Am I missing something here?