Data Studio CASE error - THEN/ELSE arguments have incompatible types: TEXT/ NUMBER

user_13137557 0
Viewed 2503

I'm trying to change my table dimension name from 1 to "Android", but I get this error message: Invalid formula - THEN/ELSE arguments have incompatible types: TEXT/ NUMBER. All THEN/ELSE arguments must return the same type.

My case statement is below:

CASE WHEN app_id = 1 THEN 'android' ELSE app_id END

google-data-studio sql
2 Answers

The below is divided into two sections, first showing the possible Issues and then the Suggested Calculated Fields:

1) Issues

1.1) Incompatible types: TEXT/ NUMBER

The below occurs as android is a Text value while app_id is a Number Type field:

CASE
  WHEN app_id = 1 THEN 'android'
  ELSE app_id 
END

Invalid formula - THEN/ELSE arguments have incompatible types: TEXT/ NUMBER. All THEN/ELSE arguments must return the same type.

1.2) No Functions in THEN/ELSE

This issue results as a functions or mathematical operation is used in the THEN/ELSE section of the CASE statement:

CASE
  WHEN app_id = 1 THEN 'android'
  ELSE CAST(app_id AS STRING )
END

THEN/ELSE statements cannot contain functions or mathematical operations.

2) Suggestions

Either of the Calculated Fields below does the trick:

2.1) RegEx

The Calculated Field below works with Text or Number fields:

REGEXP_REPLACE(CAST(app_id AS TEXT),"^(1)$","android")

2.2) Type: Text

At the Data Source, change the app_id field to a Text field (duplicate the field if required to maintain a Number and Text field) and use the CASE statement:

CASE
  WHEN app_id = "1" THEN 'android'
  ELSE app_id
END

Google Data Studio Report and a GIF to elaborate:

user_128925530

As the error message says, integers and strings are not compatible. You need to convert the integer to a string:

(CASE WHEN app_id = 1 THEN 'android' ELSE CAST(app_id as string) END)
user_11440350
Related