LinkedIn
Twitter
Hi, My use case is as follows:
I am setting up an explore for a table in bigquery whose schema looks like below:
*measures is of type repeated (array of strings)
*dimension is of type record (dim1, dim2, … are of type string)
*values is of type record (measure1, measure2, … are of type float)
Whenever I run an aggregation query in the explore (say I try to sum measure1 over the dimension dim1), the result I get is like below.
What is happening is that the SQL that runs tries to group over dim1 over all records, even where dim1 does not exist. I can get away with this by using a filter to only filter records where the measures array contains measure1. But from the end user perspective, this seems unintuitive, since the user is simply trying to aggregate measure1. Is there a way around this, i.e., not having the user specify the filter in the measure and it being done behind the scenes, like being able to ignore results if the dimension is null, or adding a filter to the measure in the LookML. Change of schema would be the last resort.
*In the LookML, there are two views, measures view which references the array, and the view which references the other columns. These two are joined (measure being un-nested) for the final explore, so any filter specifying a measure results in query where the measures column is unnested and joined and then filtered accordingly.
Thanks
