Knowledge Drop

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

  • 7 May 2021
  • 0 replies
  • 101 views

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_NAME}, do you have to join those views at the explore level?), 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.                

 

 


0 replies

Be the first to reply!

Reply