Is it possible to extend two views and use a LookML parameter in the extended view?

[greta_musu-1644]

I have two views:

• country.view

view: country {
  sql_table_name: my_project.my_dataset.country_table ;;

  dimension: geo_country {
    type: string
    sql: ${TABLE}.Geo_Country ;;
  }

  dimension: dim_spend {
     type: number
     sql: ${TABLE}.Spend ;;
     hidden: yes
  }

  measure: spend {
     type: sum
     sql: ${dim_spend} ;;
  }
}

• county.view

view: county {
  sql_table_name: my_project.my_dataset.county_table ;;

  dimension: geo_county {
    type: string
    sql: ${TABLE}.Geo_County ;;
  }

  dimension: dim_spend {
     type: number
     sql: ${TABLE}.Spend ;;
     hidden: yes
  }

  measure: spend {
     type: sum
     sql: ${dim_spend} ;;
  }
}

I created a view called demo_extensions.view (which extends country.view and county.view) with a parameter inside to filter by country or county. Here the code:

include: "country.view.lkml"
include: "county.view.lkml"

view: demo_extensions {
  extends: [country, county]

  parameter: dimension_picker {
    type: unquoted
    allowed_value: {
      label: "Country"
      value: "Country"
    }
    allowed_value: {
      label: "County"
      value: "County"
    }
  }

  dimension: dimension_from_picker {
    sql: {% if dimension_picker._parameter_value == "Country" %} ${geo_country}
         {% elsif dimension_picker._parameter_value == "County" %} ${geo_county}
         {% endif %};;
  }

  dimension: geo_country {
    type: string
  }

  dimension: geo_county {
    type: string
  }

  measure: spend {
    type: sum
  }
}

When i explore demo_extensions and filter by "Country" using the filter created via parameter, this error returns:

Query execution failed: - Name Geo_Country not found inside demo_extensions

The query executed is this:

SELECT
     demo_extensions.Geo_Country AS demo_extensions_dimension_from_picker
FROM project.dataset.county_table  AS demo_extensions
GROUP BY
    1

I don't understand why it takes from the second extended view (county) and not from the first (country).

I've read in the documentation that in case of conflicts between elements in the extends parameter, the last element is given priority. But if so, what conflicts are there in my code?
Can someone help me?

Thank you!

[Dawid]

I think you may be confusing extends with joins a little bit. Extend can only have one table (sql_table_name) not two, therefore your second one overwrites the first one. However it tries to take the fields from country view but from county table. 

In this case you may want to create the so called “bare view” that contains parameters only (no sql_table_name), join it to the explore, and use that parameter in the views. I do that with my calendar parameters

[greta_musu-1644]

Thanks for your answer!
Actually in the documentation i read that the extends parameter accepts a comma-separated list of view names. Documentation here: https://cloud.google.com/looker/docs/reference/param-view-extends

[Dawid]

That’s true but later on you can read this:

Which means that the extends are just useful DRY references of fields to be taken from a table of the view that uses extends. In your case that view is missing the table (demo_extensions) so it takes the latest one.

[greta_musu-1644]

Sorry but i still don't understand. Could you give me an example of code that you think can work?

I thank you

[Dawid]

Basically, forget about extends, just build an explore using joins just like you would query those tables in a normal SQL query

[Victor_Lorente · Accepted solution]

Hi Greta,

Actually you don’t need to go with extensions to do what you want to do.

Just use a single view with the liquid code into the `sql_table_name:` tag. You even could also use the liquid into each dimension if the column names from `country` and `county` tables would be different in some cases.

view: county_or_country {
  sql_table_name: 
    {% if dimension_picker._parameter_value == "Country" %} 
    my_project.my_dataset.country_table
    {% elsif dimension_picker._parameter_value == "County" %} 
    my_project.my_dataset.county_table
    {% endif %}
    ;;  

  parameter: dimension_picker {
    type: unquoted
    allowed_value: {
      label: "Country"
      value: "Country"
    }
    allowed_value: {
      label: "County"
      value: "County"
    }
  }    

  dimension: dim_country {
     type: string
     sql: 
       {% if dimension_picker._parameter_value == "Country" %} 
        ${TABLE}.country
      {% elsif dimension_picker._parameter_value == "County" %} 
        NULL
      {% endif %}
      ;;
     hidden: yes
  }

  dimension: dim_county {
     type: string
     sql: 
       {% if dimension_picker._parameter_value == "Country" %} 
       NULL
      {% elsif dimension_picker._parameter_value == "County" %} 
        ${TABLE}.county
      {% endif %}
      ;;
     hidden: yes
  }

  dimension: dim_county_or_country {
     type: string
     sql: 
       {% if dimension_picker._parameter_value == "Country" %} 
        ${TABLE}.country
      {% elsif dimension_picker._parameter_value == "County" %} 
       ${TABLE}.county 
      {% endif %}
      ;;
     hidden: yes
  }

}

Hope this helps.

[greta_musu-1644]

Thank you! It was very helpful!