5

I seek to create a field in Google Data Studio using a CASE statement including a date column and a datetime column. Whenever the date column Start_Time is NULL, the datetime column Given_Timestamp is not NULL.

The issue

Whenever Start_Time is not filled, the date for visuals has to be taken from Given_Timestamp. I tried the statement:

CASE
  WHEN Start_Time IS NULL THEN TODATE(Given_Timestamp, 'DEFAULT_DASH', '%d%m%Y')
  ELSE Start_Time
END

(Note that TODATE(Timestamp,'DEFAULT_DASH','%d%m%Y') gives the date in the same form as Start_Time when listing them in a table visual.)

It throws the error message:

Failed to parse CASE statement.

What am I missing?

Details

In the source Google sheet, Given_Timestamp is filled automatically via a Google form. Since the datetime format is no standard in Google Data Studio, I have to import it to Data Studio as a string.

I already tried to convert the datetime column to a date column in a separate field to then use that field in the CASE statement. In this case, there is no error message. However, there seems to be an issue with the resulting field. I can't use it for any visual:

Visual in Google Data Studio when using the resulting field

Community
  • 1
  • 1
Michelle Turner
  • 314
  • 5
  • 22

1 Answers1

2

You can only use fields which are already created in a CASE statement.

So, in this case, you need to first create calculated field like

dateFromTimestamp = TODATE(Given_Timestamp, 'DEFAULT_DASH', '%d%m%Y')

and then in the case statement use this field

CASE WHEN Start_Time IS NULL THEN dateFromTimestamp ELSE Start_Time END
Maaz Contractor
  • 21
  • 1
  • I already tried your solution and the error message disappeared. However, the result seems to be a date field filled with only NULLs. I am unable to successfully use the new field for any visual. – Michelle Turner Mar 04 '19 at 13:15