Question

templated filters and derived tables

  • 22 November 2022
  • 0 replies
  • 10 views

view: closed_loans {
  derived_table: {
    sql:
    with closed_loan_data as (
      select row_number() over (order by coalesce(closed_loan_close_date, lead_created_date) desc) as rnk,
      *
      from `renofidata.analytics_production.prod_borrowers`
      where closed_loan_id is not null
      and closed_loan_close_date <= ifnull({% parameter end_date %}, current_timestamp)
      and {% condition filter_loan_type_category %} loan_type_category {% endcondition %}
      and {% condition include_null_leads %} lead_id {% endcondition %}
      order by closed_loan_close_date desc
    )
    select *
    from closed_loan_data
    where rnk between 1 and {% parameter top_n %}
    ;;
  }

 

I have been trying to pass a template filter to the derived query. In the line “ {% condition include_null_leads %} lead_id {% endcondition %}”, I have been passing:
parameter: include_null_leads {
    type: string
    allowed_value: {
      label: "yes"
      value: ""
    }
    allowed_value: {
      label: "no"
      value: "NOT NULL"
    }
  }

 

The issue I am facing is that if I am pass value = “NULL”, looker converts it as lead_id is NULL. But, when I pass value: “NOT NULL”, looker translates it as lead_id = “Not NULL”. 

 

What can I have in the value so that the looker translates it as lead_id is not null. 


0 replies

Be the first to reply!

Reply