Obtaining both a suggested parameter and its label from a different table

Looker Q&A Looker
1

Hello everyone!

I have a question about (suggested) parameters.

For a certain explore, I want the end-user to be able to select which table will be queried. However, instead of giving the end user a suggested “table_name” to choose from, I want Looker to suggest a “label” corresponding to the “table_name”, to make it more user-friendly.

What I currently have done is as follows:

  • I have an explore called “all_tables”.
  • This explore consists of 1 view with 2 dimensions: “table_name” and “label”.
  • “all_tables” thus looks like this:
table_name label
schema_name.table_1 “foo”
schema_name.table_2 “bar”
schema_name.table_3 “etc”

I’m using “all_tables” to obtain suggested values for a parameter by creating the following view:

view: dynamic_label {
  derived_table: {
    sql:
    SELECT id, timestamp
    FROM {% parameter table_filter %};;
  }

  parameter: table_filter {
    type: unquoted
    suggest_explore: all_tables
    suggest_dimension: all_tables.table_name
    }

  dimension: id {
    primary_key: yes
    type: string
    sql: ${TABLE}.id ;;
  }

  dimension: timestamp {
    type: date
    sql: ${TABLE}.timestamp ;;
  }
}

Currently, when the end user chooses the parameter “table_filter”, he/she gets “table_name”s suggested as possible values. However, like mentioned, I’d like to replace the suggested value with the “label”, while still querying from the corresponding “table_name”.

Does anyone happen to have experience doing so?

Thanks in advance!

2

I’m not 100% sure, but I believe the closest solution I’ve heard of is to instead suggest “Label - ID” pairs, and then use some liquid to drop the label part from the user’s selection, something like {% assign parts = my_param._parameter_value | split: " - " %}

1 Like
3

Hey Fabio!

Thanks, used/altered your solution a bit to get what I needed. Current setup doesn’t work in all intended cases yet but I got a step further!