Dynamically call constant

[Adrian_Triteanu]

Hey guys,
does anyone know if it is possible to dynamically call a constant based on one parameter the user selects?

I have two constants defined in the manifest file:

constant: schema_version__de {
value: “v1”
export: none
}
constant: schema_version__at {
value: “v2”
export: none
}

I defined a parameter:

parameter: country_code {
    type: unquoted
    default_value: "de"
    allowed_value: {
      label: "DE"
      value: "de"
    }
    allowed_value: {
      label: "AT"
      value: "at"
    }
  }
}

and inside a view i need to create the mapping to a table:

sql_table_name: `my_database.@{schema_version__{% parameter country.country_code %}}

so that the constant is picked up based on the user country selection.
Unfortunately this does not work.
The output is:

SELECT
*
FROM my_database.@{schema_version__de}.my_table

Thank you.

[Cyril_MTL_Analy]

Hello @Adrian_Triteanu and welcome!

Yes that’s possible 🙂 but with a tweak compared to what you’re doing.

try this code:

 sql_table_name: `my_database.{%
     if parameter country.country_code == 'de' %}@{schema_version__de}{% 
     elsif parameter country.country_code == 'at' %}@{schema_version__at}{% 
     {% else %}<your default value>{%
     endif %} 

link to doc

[Adrian_Triteanu]

Thank you so much for your reply.
I had the
if .. then ..
solution in my head too… but TBH i was keeping this as a last resort.
We have more than 100 views and changing, adding or removing countries would be a big pain in the …
Thanks again for your reply.

[Cyril_MTL_Analy]

Another solution then: why not using the value of the parameter in the sql_table_name instead of a constant?

parameter: country_code {
    type: unquoted
    default_value: "de"
    allowed_value: {
      label: "DE"
      value: "v1"
    }
    allowed_value: {
      label: "AT"
      value: "v2"
    }
  }
}

sql_table_name: `my_database.{% parameter country.country_code %}`

[i_plotnikov-165]

@Cyril_MTL_Analy thank you for your solution

It might be tedious though to put such a cumbersome statement into every single view. Especially if you have dozens of parameter options. Is there a way to get desired value for a specific key with a query to a database table or something like that?

[Dawid]

You don’t have to put this statement in each view. You can actually create one view “countr_parameter” and treat it as sometimg called “bare view” and join it to your explores. 

I do that with currency conversions

[i_plotnikov-165]

@Dawid what if I need to restrict list of parameters available for selection individually for every single user? I can do that with filtering user attributes in some view, applying ‘suggest_dimension’ to parameter, but it seems not possible with a “bare view”...

[Dawid]

If you need to change it based on something than it is no longer the same for everything, hence no, bare view wouldn’t work anymore as it’s good if the parameter is used in the same version everywhere

[i_plotnikov-165]

don’t know how to make this kind of template work though:

FROM {% if parameter parameters.domain_prefix_selected == 'a' %}
      schema_1
    {% elsif parameter parameters.domain_prefix_selected == 'b' %}
      schema_2
    {% elsif parameter parameters.domain_prefix_selected == 'c' %}
      schema_3
    {% endif %}
    .warehouse_orders

result: 

The Amazon Aurora MySQL database encountered an error while running this query.

unknown escape sequence {% if parameter parameters.domain_prefix_selected == 'a' %}

[vikram1]

@i_plotnikov-165  You don't need to place parameter parameters.domain_prefix_selected.  If you want to compare the selected value of parameter value with something, just use the parameter_name._parameter_value

FROM {% if parameter domain_prefix_selected._parameter_value == 'a' %}
      schema_1
    {% elsif parameter domain_prefix_selected._parameter_value == 'b' %}
      schema_2
    {% elsif parameter domain_prefix_selected._parameter_value == 'c' %}
      schema_3
    {% endif %}
    .warehouse_orders

[i_plotnikov-165]

@vikram1 

not working ?

The Amazon Aurora MySQL database encountered an error while running this query.

unknown escape sequence {% if parameter domain_prefix_selected._parameter_value == 'a' %}

[Deepthi1]

I want to use user attributes for dynamic schema and table name injection in sql_table_name parameter in LookML. However My dataset looks like datasetname_region1.table_name for one looker spoke project and datasetname_region2.table_name for another spoke project. I also want to append dataset suffix as _dev to my data set name along with region, it will look like datasetname_EU_dev.table name. Looker Hub project is where we have the code and we use extensions to in spoke projects to get the respective dataset name. Hence I want to update the sql table name like what I mentioned in Hub. For this reason, am trying to use a constant for region and user attribute for dataset suffix and define the sql table name in hub but am not able to combine both. it gives me dataset not found error. Hence am looking for other options where in i can define the region for region1 and region2 in manifest file using any method and combine it with dataset suffix (_Dev) user attribute. please let me know if we can achieve it and if yes how to do