I'm trying to create a filtered custom measure to display a single value. The view being referenced contains many values related to a "product" and I'm trying to create a column for specific field values.
When I attempt to use this custom field, looker returns an error:
I changed to using a custom filter and looker generates the query correctly. I worked around the above error but looker still doesn't display any results. The weird part is that Looker is generating the SQL correctly and is exactly what I want for my column.
STRING_AGG(DISTINCT CAST(CASE WHEN product_custom_field.external_field_id = 'station-code' THEN product_custom_field.field_value ELSE NULL END AS STRING), '|RECORD|') AS station_code
Anyone have any tips or pointers to achieve this?
For reference here is the view itself:
view: product_custom_field {
sql_table_name: `@{project}.@{partition_id}.ProductCustomField` ;;
dimension_group: created {
type: time
description: "The time at which the custom field was created."
timeframes: [raw, time, date, week, month, quarter, year]
sql: ${TABLE}.created ;;
}
dimension: external_field_id {
type: string
description: "The unique identifier for the custom field set by the partner."
sql: ${TABLE}.external_field_id ;;
}
dimension: field_name {
type: string
description: "The name of the custom field."
sql: ${TABLE}.field_name ;;
}
dimension: field_value {
type: string
description: "The value of the custom field."
sql: ${TABLE}.field_value ;;
}
dimension: internal_field_id {
type: string
description: "The unique identifier for the custom field set by the system."
sql: ${TABLE}.internal_field_id ;;
}
dimension: product_id {
type: string
description: "The unique identifier for the product."
sql: ${TABLE}.product_id ;;
}
dimension_group: updated {
type: time
description: "The time at which the custom field was last updated."
timeframes: [raw, time, date, week, month, quarter, year]
sql: ${TABLE}.updated ;;
}
measure: count {
type: count
drill_fields: [field_name]
}
measure: list {
list_field: field_value
type: list
}
measure: sum {
type: sum
sql: SAFE_CAST(${TABLE}.field_value as integer);;
}
}
Solved! Go to Solution.
Yeah you probably could use parameters for this: https://cloud.google.com/looker/docs/reference/param-field-parameter#parameters_of_type_string
Then whoever is exploring just has to choose the field they want in the filter value
This is currently a built-in limitation of custom fields not based on numeric types. I would recommend checking out https://www.googlecloudcommunity.com/gc/The-Kitchen-Table/Feature-Requests-How-your-feedback-makes-L... to file feedback for this to be supported.
In the meantime the easiest way to achieve what you want is likely just going to be to make a LookML measure of type string that has that SQL that Looker generated above. So something like:
measure: station_code
type: string
sql: STRING_AGG(DISTINCT CAST(CASE WHEN product_custom_field.external_field_id = 'station-code' THEN product_custom_field.field_value ELSE NULL END AS STRING), '|RECORD|') AS station_code ;;
Yeah you probably could use parameters for this: https://cloud.google.com/looker/docs/reference/param-field-parameter#parameters_of_type_string
Then whoever is exploring just has to choose the field they want in the filter value
Thanks, have it working for a filtered field. Now, to update to use parameters to allow multiple filters be specified.
Its truly unfortunate that string measures aren't support as a custom dimension as that would make this so much easier.
measure: filtered_field_value {
type: string
sql:
{% if product_custom_field.external_field_id._is_filtered %}
STRING_AGG(
DISTINCT CAST(
CASE
WHEN {% condition product_custom_field.external_field_id %} product_custom_field.external_field_id {% endcondition %} THEN product_custom_field.field_value ELSE NULL
END
AS STRING), '|RECORD|')
{% else %}
""
{% endif %} ;;
}