Odd behavior with yesno dimension

ChrisB
Participant I

Hello!! I have a Looker report that uses a view. It relies on a derived table built thusly

view: cte_seller_revenue_past_12_months {
derived_table: {
sql: SELECT
seller.id as seller_id,
true as revenue_past_12_months
FROM ...
JOIN ...
JOIN ...
WHERE contract_entry.INCOME > 0
...
...
GROUP BY 1,2
HAVING COUNT(DISTINCT (TO_CHAR(DATE_TRUNC('month', CAST(contract_entry.OCCURRED_TIME AS TIMESTAMP_NTZ) ), 'YYYY-MM'))) = 12
ORDER BY 1 ;;
}

The LookML has two dimensions

  dimension: seller_id {
type: string
hidden: yes
sql: ${TABLE}.seller_id ;;
}

dimension: revenue_past_12_months {
type: yesno
sql: ${TABLE}.revenue_past_12_months = 'True';;
}
}

When I add my `revenue_past_12_months` as filter, it behaves oddly. If I select NO, I would expect to retrieve 0 records but it does not work (I get data back). If I select YES, I get data back.

Looking at the SQL code for the report itself, I noticed the following

WITH cte_seller_revenue_past_12_months AS (SELECT
seller.id as seller_id,
true as revenue_past_12_months
FROM ...)
SELECT
seller.SELLER_ID AS "seller.seller_id",
seller.COMPANY_ID AS "seller.company_id",
(CASE WHEN cte_seller_revenue_past_12_months.revenue_past_12_months = 'True' THEN 'Yes' ELSE 'No' END) AS "cte_seller_revenue_past_12_months.revenue_past_12_months"
...
FROM DBT.CONTRACT_ENTRY_MART AS contract_entry
LEFT JOIN DBT.COMPANIES_MART
AS seller ON contract_entry.SELLER_ID = seller.MEMBERSHIP_ID
LEFT JOIN cte_seller_revenue_past_12_months ON seller.SELLER_ID = cte_seller_revenue_past_12_months.seller_id
WHERE ... AND (NOT (cte_seller_revenue_past_12_months.revenue_past_12_months = 'True') OR (cte_seller_revenue_past_12_months.revenue_past_12_months = 'True') IS NULL)
GROUP BY
1,
2,
3
ORDER BY
1
FETCH NEXT 500 ROWS ONLY

Why is Looker adding an OR (cte_seller_revenue_past_12_months.revenue_past_12_months = 'True') IS NULL  ?? If I remove it, the filter runs successfully.

I know I am missing something here. What do I need to do to make my filter work? Any advice?

0 5 458
5 REPLIES 5

n_davis
Participant V

I’ve used coalesce in the sql to have things like this work. So if the default would be false then in your yesno field add: 

sql: coalesce(${TABLE}.revenue_past_12_months, 'False') = 'True';;

ChrisB
Participant I

I’ve used coalesce in the sql to have things like this work. So if the default would be false then in your yesno field add: 

sql: coalesce(${TABLE}.revenue_past_12_months, 'False') = 'True';;

The coalesce works fine but it does not remove the unnecessary OR statement. 

n_davis
Participant V

@ChrisB it is because of the filter - so if you use ‘Yes’ as the filter, it won’t have the or statement. However, since yesno is nullable Looker adds the OR for the ‘No’ piece. I don’t think there is a workaround aside from the coalesce or changing the field type to be something different.

ChrisB
Participant I

@ChrisB it is because of the filter - so if you use ‘Yes’ as the filter, it won’t have the or statement. However, since yesno is nullable Looker adds the OR for the ‘No’ piece. I don’t think there is a workaround aside from the coalesce or changing the field type to be something different.

Oh yeah, I get it. That’s logical. Will check and report back. Thank you.

ChrisB
Participant I

The 

@ChrisB it is because of the filter - so if you use ‘Yes’ as the filter, it won’t have the or statement. However, since yesno is nullable Looker adds the OR for the ‘No’ piece. I don’t think there is a workaround aside from the coalesce or changing the field type to be something different.

Oh yeah, I get it. That’s logical. Will check and report back. Thank you.

  1. I changed the field type from yesno to string
  2. and added the coalesce statement.
  3. At runtime, both yes and no scenarios do generate the correct code ie without NULL.
  4. The query however is still returning records when no is selected.

That’s when I realized that inside the derived table code, there is the WHERE contract_entry.INCOME > 0  clause. I completely overlooked that.

Unless I am mistaken, as long as this WHERE clause remains in the derived table code, the filter will never work. The issue is not really with the code of that field. 

Since I am not the original author of this code, I reached out to the end user and I am waiting to hear back.

Top Labels in this Space
Top Solution Authors