View the original community article here
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.