Unable to reference yesno parameter value in sql

done
liquid
low_priority
watch

#1
with cte as (
...
)
select cte.* from cte
    {% if apply_top_n_filter._parameter_value == 'Yes' %}
      join (select state from cte group by 1 order by sum(profit) desc limit {% parameter top_n_syscode %}) rnk
      on cte.state = rnk.state
    {% endif %}

parameter: apply_top_n_filter {
    type: yesno
  }

I have a derived table and a yesno parameter above. I was trying to say if “apply_top_n_filter” is “Yes” then it would inner join with the ranking of itself to act like a filter. However, the join statement within the liquid if statement never show up even the parameter is selected as “Yes”.

I’ve tried

  • {% if apply_top_n_filter._parameter_value == ‘Yes’ %}
  • {% if apply_top_n_filter._parameter_value == ‘yes’ %}
  • {% if apply_top_n_filter._parameter_value == “‘Yes’” %}
    but none of these work.
    What is wrong with my lookML code?
    Thank you.

(Jackson Alvarez) #2

I didn’t know the answer here, but there’s a simple way to test what the parameter value is. return the parameter value!

If you change the derived table syntax to just say

    {% if apply_top_n_filter._parameter_value == 'Yes' %}
    {{ apply_top_n_filter._parameter_value }}
      join (select state from cte group by 1 order by sum(profit) desc limit {% parameter top_n_syscode %}) rnk
      on cte.state = rnk.state
   {% else %}
     {{ apply_top_n_filter._parameter_value }}
      you picked no
    {% endif %}

Then, by looking at the SQL tab you’ll be able to see what the real back-end value of the parameter is. we can’t get the “yes” to evaluate, but it looks like “No” evaluates to false. So I bet “yes” evaluates to ‘true’, and that’s what you should have in your if statement. Let me know if that’s right!


#3

Thank you!! I tried this and the yesno parameter value in sql is ‘true’ and ‘false’.