Hi there,
Would like some input on how to create a dimension from an array column in Lookml.
Warm regards.
Which dialect do you use? I’ve used array with Snowflake and BigQuery. As long as unnesting an array doesn’t impact performance too much, I use principles outline on this page:
https://help.looker.com/hc/en-us/articles/360023638874-Nested-Data-in-BigQuery-Repeated-Records-
Which dialect do you use? I’ve used array with Snowflake and BigQuery. As long as unnesting an array doesn’t impact performance too much, I use principles outline on this page:
https://help.looker.com/hc/en-us/articles/360023638874-Nested-Data-in-BigQuery-Repeated-Records-
I am using Bigquery, I had referred the article. However I feel as though my problem is in defining the View to join with the master1. Could you please have a look
connection: "bigquery"
## trying to define an array column based on the reference link but having errors. Below is a snippet(adjusted) of my SQL codes.
## working with two views "Master1" and "Array_column" with the second being the array column. Also, the array in the database is termed "Ownersname1" with the names repeating.
explore: Master 1 {
# Repeated nested object
join: Array_column {
view_label: "Array Names"
sql: LEFT JOIN UNNEST(Master1.Ownnername1) as Owners_name ;;
relationship: one_to_many
}
# Non repeated nested object
join: persons_phone_number {
view_label: "Persons: Phone:"
sql: LEFT JOIN UNNEST([${persons.phoneNumber}]) as persons_phone_number ;;
relationship: one_to_one
}
}
view: Master1 {
sql_table_name: `bigquery-samples.nested.persons_living` ;;
dimension: id {
primary_key: yes
sql: ${TABLE}.fullName ;;
}
dimension: gender {}
dimension: fullName {label: "Full Name"}
dimension: age {type:number}
measure: count {
type: count
drill_fields: [fullName, age]
}
dimension: Ownersname1 {hidden:yes}
view: Array_Column {
dimension: id {
primary_key: yes
sql: CONCAT(CAST(${Master1.fullName} AS STRING),'|', CAST(${place} AS STRING)) ;;
}
dimension: place {}
dimension: numberOfYears {
label: "Number Of Years"
type: number
}
}