AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Data Studio
2.6K members online now
2.6K 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?