I am just wondering if this is possible:
view: finance_services {
derived_table: {
sql:
{% if parameter.locale == "all" %}
SELECT different statement FROM table
{% else %}
SELECT statement FROM table
{% endif %}
;;
}
The reason for it is that this query has some ranking based on a grouping but I want to be able to do calculations for totals as well. I want to avoid putting liquid in 10 different places in the query (select fields, group by, joins, window functions) and would prefer to have the whole query as conditional.
Is it possible? Is it even the best way to approach it?
Solved! Go to Solution.
Yes, this is possible! We do something similar for query speed purposes with our time-series data, where we select which summary table to pull from based on how the user is grouping the data:
view: usage_distributions_small {
derived_table: {
sql: SELECT * FROM
{% if select_date_window._parameter_value == "'week'" %} analytics.daily_usage_summary_week
{% elsif select_date_window._parameter_value == "'month'" %} analytics.daily_usage_summary_month
{% elsif select_date_window._parameter_value == "'year'" %} analytics.daily_usage_summary_year
{% elsif select_date_window._parameter_value == "'school_year'" %} analytics.daily_usage_summary_school_year
{% else %} analytics.daily_usage_summary_school_year
{% endif %} base
WHERE {% condition select_date_range %}
{% if select_date_window._parameter_value == "'school_year'" or select_date_window._parameter_value == "'year'" %} base.min_daydate
{% else %} base.date_window
{% endif %}
{% endcondition %}
;;
}
# dimensions and measures go here
}
We’re not doing any separate grouping/joins/etc in this example, but there are other places we do that (the code would just be too big to fit here).
These are the biggest things we’ve had to think about:
Yes, this is possible! We do something similar for query speed purposes with our time-series data, where we select which summary table to pull from based on how the user is grouping the data:
view: usage_distributions_small {
derived_table: {
sql: SELECT * FROM
{% if select_date_window._parameter_value == "'week'" %} analytics.daily_usage_summary_week
{% elsif select_date_window._parameter_value == "'month'" %} analytics.daily_usage_summary_month
{% elsif select_date_window._parameter_value == "'year'" %} analytics.daily_usage_summary_year
{% elsif select_date_window._parameter_value == "'school_year'" %} analytics.daily_usage_summary_school_year
{% else %} analytics.daily_usage_summary_school_year
{% endif %} base
WHERE {% condition select_date_range %}
{% if select_date_window._parameter_value == "'school_year'" or select_date_window._parameter_value == "'year'" %} base.min_daydate
{% else %} base.date_window
{% endif %}
{% endcondition %}
;;
}
# dimensions and measures go here
}
We’re not doing any separate grouping/joins/etc in this example, but there are other places we do that (the code would just be too big to fit here).
These are the biggest things we’ve had to think about: