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

[jmeij]

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!

[fabio1 · Accepted solution]

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: " - " %}`

[jmeij]

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!