CODEWITHSUNDEEP
looker-studio
doğukan
doğukan

Reputation: 27431

Data Studio Case Statement - How to return original value?

I'm trying to make a calculated field. This is the Case statement I write.

case Rank
  when 0 then '-'
end

This works as expected but returns null for other values:

enter image description here

I want to show original value for values other than 0 (in this case, should be 1 instead of null). I tried this:

case Rank
  when 0 then '-'
  else Rank
end

but got this error:

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

enter image description here

I couldn't find any example. Is there any way to do this?

Upvotes: 1

Views: 807

Answers (1)

Nimantha
Nimantha

Reputation: 6471

The Error in the second CASE statement is displayed because the Semantic Type (referred to as Type from here on) of the Rank field is Number whilst the THEN portion on line 2 (the dash -) is a Text Type:

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

The solutions below involve ensuring that all output is of a single Type, (in this case Text); there are a couple of approaches (use EITHER #1 or #2 below):

1) Change Type of Rank to Text in a Calculated Field

One approach is to incorporate the CAST function to change the Field Type to Text; the entire process can be done in a single Calculated Field using the REGEXP_REPLACE function:

REGEXP_REPLACE(CAST(Rank AS TEXT ), "^(0)$", "-" )

2) Change Type of Rank to Text at the Data Source

The second way is to change the Field Type of Rank at the Data Source and use the second CASE statement with a small edit (wrapping 0 in quotes, thus 0 becomes "0"):

CASE Rank
  WHEN "0" THEN '-'
  ELSE Rank
END

Editable Google Data Studio Report and a GIF to elaborate:

Upvotes: 1

Related Questions