Question

Dynamically call constant


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.


8 replies

Userlevel 5
Badge

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

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.

Userlevel 5
Badge

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

@Cyril_MTL_Analytics 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?

Userlevel 7
Badge +1

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

 

@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”...

Userlevel 7
Badge +1

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

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' %}

Reply