CODEWITHSUNDEEP
sqldatetimegoogle-sheetsgoogle-formslooker-studio
Michelle Turner
Michelle Turner

Reputation: 324

How do I combine two date fields in Google Data Studio?

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

Upvotes: 5

Views: 2830

Answers (1)

Maaz Contractor
Maaz Contractor

Reputation: 21

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

Upvotes: 2

Related Questions