Using a Filter value to execute only part of Derived Query

[riteshm]

I have the following derived query:

derived_table: {
    sql:select * from this_year

union all

select * from last_year ;; }

I have the following filter:

filter: this_year_last_year_filter {
    label: "This Year Last Year Analysis"
    type: string
    suggestions: ["No", "Yes"]
  }

what I am trying to achieve is, based on what the filter value is selected the derived query should be updated. if the this_year_last_year_filter = Yes then only execute the second part of the union all else just execute the first part of the query.

This is what is expected:

derived_table: {
    sql:

select * from this_year

{% if  this_year_last_year_filter.value == “Yes”%}

union all

select * from last_year ;;

{% endif %}

}

how to achieve this ? any help would be greatly appreciated.

[leobardor]

Hi Riteshm!

I think your sample code is close.

Please find this example :

view: store_current_one_store {
  derived_table: {
    sql: SELECT
          (DATE(store_revenue_current."store_revenue_date" )) AS "store_revenue_current.store_revenue_date",
          store_revenue_current."store_number_code"  AS "store_revenue_current.store_number_code"
      FROM public.store_revenue_current  AS store_revenue_current
      {% if store_lists._parameter_value != "'all'" %}
      WHERE (store_revenue_current."store_number_code" ) = {% parameter store_lists %}
       {% endif %}
      GROUP BY
          1,
          2
      ORDER BY
          1 DESC
       ;;
  }


  parameter: store_lists {
    type: string
    default_value: "all"
    allowed_value: {
      label: "First Store - 8501"
      value: "8501"
    }
    allowed_value: {
      label: "Second Store - 8502"
      value: "8502"
    }
    allowed_value: {
      label: "All Stores"
      value: "all"
    }
  }

Best regards!

Leo