CODEWITHSUNDEEP
google-bigquery
Simon Breton
Simon Breton

Reputation: 2876

using where statement with flatten custom dimension

I've build the following query :

SELECT
  MAX(IF (hits.customDimensions.index = 10, hits.customDimensions.value, NULL)) WITHIN RECORD AS postId,
  DATE(MAX(IF (hits.customDimensions.index = 4, hits.customDimensions.value, NULL))) WITHIN RECORD AS Datepublished,
  MAX(IF (hits.customDimensions.index = 1, hits.customDimensions.value, NULL)) WITHIN RECORD AS Country,
  MAX(IF (hits.customDimensions.index = 7, hits.customDimensions.value, NULL)) WITHIN RECORD AS Author,
FROM
  (TABLE_DATE_RANGE([storied-toolbox-145015:102152044.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))

however I would like to be able to use a where statement based on one of the custom dimension. something like this where Country = 'fr'. I've tried various solution however I'm always stuck with Over clause error or scope error.

Since I want to use this query within Google Data Studio I need to build it with SQL legacy.

thanks !

Upvotes: 2

Views: 397

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173056

... I would like to be able to use a where statement based on one of the custom dimension

Below assumes that your query works as you expected so it only addressing "filtering" by country which is essentially the question! 

SELECT
  MAX(IF (hits.customDimensions.index = 10, hits.customDimensions.value, NULL)) WITHIN RECORD AS postId,
  DATE(MAX(IF (hits.customDimensions.index = 4, hits.customDimensions.value, NULL))) WITHIN RECORD AS Datepublished,
  MAX(IF (hits.customDimensions.index = 1, hits.customDimensions.value, NULL)) WITHIN RECORD AS Country,
  MAX(IF (hits.customDimensions.index = 7, hits.customDimensions.value, NULL)) WITHIN RECORD AS Author,
FROM
  (TABLE_DATE_RANGE([storied-toolbox-145015:102152044.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
HAVING Country = 'fr'

As you can see the "trick" is in using HAVING here instead of WHERE

Upvotes: 1

Related Questions