Complete name of field (sql) using liquid parameter

[Rub_moy]

Dear Experts,
In the table im working with there are multiple fields named according to the currency their value represents. Examples:
Names in DB
${TABLE}.AmountUSD
${TABLE}.AmountEUR
${TABLE}.FeeUSD
${TABLE}.FeeEUR

I've created the following parameter to create a dimension that allows me to switch between currencies EUR/USD:

  parameter: selector_currency {
    type: unquoted
    label: "Parameter currency"
    default_value: "EUR"
    allowed_value: {
      label: "EUR"
      value: "EUR"
    }
    allowed_value: {
      label: "USD"
      value: "USD"
  }
  }
  
  dimension: dim_selector_currency{
    label: "Selector currency"
    label_from_parameter: selector_currency
    type: string
    sql: {% if selector_currency._parameter_value == "EUR" %} EUR
            {% else %} USD
              {% endif %}
            ;;
  }

Since I've got many fields with multiple currencies, my idea is to create one variable field for each. Example:

measure:total_variable_amount {
label:"total amount"
type: sum
sql: ${TABLE}.Amount${parameterview.dim_selector_currency} ;;
}

measure:total_variable_fee {
label:"total fee"
type: sum
sql: ${TABLE}.Fee${parameterview.dim_selector_currency};;
}

 But I get an error cause Looker puts a space ' ' between 'Amount' and my parameter. Example
viewname.Amount EUR

I've tried to create a parameter where I used the entire name of the field. Example:

  dimension: dim_selector_currency{
    label: "Selector currency"
    label_from_parameter: selector_currency
    type: string
    sql: {% if selector_currency._parameter_value == "EUR" %} AmountEUR
            {% else %} AmountUSD
              {% endif %}
            ;;
  }

Then i create a dimension :

measure:total_variable_amount {
label:"total amount"
type: sum
sql: ${TABLE}.${parameterview.dim_selector_currency} ;;
}

And it works fine. The problem with that is that I'd need to create multiple parameters.
Does anybody know how to modify part of the sql field name using liquid ??
Thanks in advance!

[salehaxsid]

You can try liquid after creating dimension or measure of required field and refer it in another dimension or measure of your choice. 

For example:
dimension: abc{

type: 
sql: {%if condition%} dimension or measure name {%endif%}

}

[Jacqui_N · Accepted solution]

Hi @Rub_moy!

You're definitely on the right track with this logic, but you can make it even simpler! Right now you have a dimension that uses if/then statements indicating the user's selection. You can actually use only the parameter selector_currency to accomplish what you're trying to do. The reason why your current logic is not working is because you create the extra dim_selector_currency dimension that's a string type instead of unquoted. By referencing the original selector_currency parameter, you ensure that EUR and USD are unquoted and attached to the field name while also avoiding having to do additional if/then statements in another dimension. Hope this helps!

  parameter: selector_currency {
    type: unquoted
    label: "Parameter currency"
    default_value: "EUR"
    allowed_value: {
      label: "EUR"
      value: "EUR"
    }
    allowed_value: {
      label: "USD"
      value: "USD"
    }
  }
measure:total_variable_amount {
label:"total amount"
type: sum
sql: ${TABLE}.Amount{% parameter selector_currency %} ;;
}

measure:total_variable_fee {
label:"total fee"
type: sum
sql: ${TABLE}.Fee{% parameter selector_currency %};;
}

[Rub_moy]

Thanks a lot @Jacqui_N ! 🙂