"Variable not found" when dynamically querying derived tables using parameters

Knowledge Drop

Last tested: Nov 26, 2020
 

Are you using the method outlined in Dynamically Query Tables Using Parameters with derived tables and hitting a pesky "Variable not found" error message in the LookML Validator?

While derived tables in the format ${derived_table_or_view_name.SQL_TABLE_NAME} don't need to be joined (see If you reference a derived table in the SQL of another derived table, using ${viewname.SQL_TABLE_NAM...), they need to be joined if we're using a parameter. This is because the views must be joined in order to "listen" to the parameter.

Example:

view: selected_table {



sql_table_name:

{% if timeframe_selection._parameter_value == 'weekly' %}

${weekly_view.SQL_TABLE_NAME}

{% elsif timeframe_selection._parameter_value == 'daily' %}

${daily_view.SQL_TABLE_NAME}

{ % else % }

${full_view.SQL_TABLE_NAME}

{ % endif % } ;;



parameter: timeframe_selection {

type: unquoted

allowed_value: {

label: "Weekly"

value: "weekly"

}

allowed_value: {

label: "Daily"

value: "daily"

}

allowed_value: {

label: "All"

value: "all"

}

}



}

With just this code, we will hit a "Variable not found" error that will point back to one of the derived tables. To resolve this error, we need to have all four views joined together in the explore object:

explore: selected_table{



join: daily_view {

relationship: many_to_one

sql_on: 1=1 ;;

fields: []

}



join: weekly_view {

relationship: many_to_one

sql_on: 1=1 ;;

fields: []

}



join: full_view {

relationship: many_to_one

sql_on: 1=1 ;;

fields: []

}

}

 

This content is subject to limited support.                

Version history
Last update:
‎05-07-2021 09:48 AM
Updated by: