Qualify in LookML - Workaround

[Dmitry_Shmurie1]

Hello! Attempting to use the “QUALIFY” sql statement (working with Snowflake), I found pretty tricky but elegant solution to use that:

1. Declare a measure with partition by in “sql”
2. In an explore use sql_always_having attribute with the bare condition (1=1) and then write the “QUALIFY” keyword along with the conditions required.

measure: my_mes {
    type: number
    sql: SUM(${mes}) OVER (PARTITION BY ${my_dim}) ;;
  }

------------ explore ------------

  sql_always_having: 1=1
QUALIFY
  ${my_mes} >= 100000000
;;

[Dmitri_S]

@Dmitry_Shmurie1 good workaround!

"1=1" before qualify allows to not break measure filtering, which executes by having clause!

Extended version allows to have multiple conditions using constants - 

sql_always_having: (1=1) QUALIFY (1=1) 
@{qualify_condition_1} # AND SUM(A) over () > X
@{qualify_condition_2} # AND SUM(B) over () > Y

[prabhakaran_mai]

---

@Dmitri_S wrote:

"1=1" before qualify allows to not break measure filtering, which executes by having clause!

---

Interesting , Would you be able to explain this little bit more?

[Dmitri_S]

Sure @prabhakaran_mai - I meant that if you additionally have to filter by any measure, Looker would add having condition automatically and you have to handle it. We use liquid conditions to analyse if any measure is filtered and based on it put a proper "Qualify workaround". See the abstract example below -

lookmkl:
{%- if is_monitoring_measures_filtered_condition -%}
(1=1)) QUALIFY ((1=1)
{%- else -%} (1=1) QUALIFY (1=1) {%- endif -%}

SQL:
select ...
having (msr1_condition and (1=1)) QUALIFY ((1=1) 
@{qualify_condition_1} # AND SUM(A) over () > X
@{qualify_condition_2} # AND SUM(B) over () > Y
)