Search code examples
looker-studiocase-statement

Combined Column in Google Data Studio

I am having trouble combining separate columns with null values into one.

The example table I have contains values like so

Col A Col B Col C
null 100 null
55 null null
null null 27

I want to write the statement such that I get a column that contains all the values.

combined
100
55
27

I have tried a few different ways but I am still getting nulls in my combined column.

The latest attempt was :

CASE 
WHEN Col A IS NOT NULL THEN Col A
WHEN Col B IS NOT NULL THEN Col B
WHEN Col C IS NOT NULL THEN Col C
END

I thought this would work because GDS's page on case statements says it returns the first true value but my result is all nulls.

Any help is appreciated.

Solution

  • It can be achieved by incorporating the NARY_MAX function below, which ensures that NULL values are treated as the numeric literal 0:

    NARY_MAX(Col A, 0) + NARY_MAX(Col B, 0) + NARY_MAX(Col C, 0)

    Editable Google Data Studio Report and a GIF to elaborate: