Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

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.

10 replies

I

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

Dawid
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
I

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

Dawid
I

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

V

@i.plotnikov-1651070196  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

@vikram.singh-1632924966 

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

Cyril_MTL_Analytics
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

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

Reply


Terms of Service