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?
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';;
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.
@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 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.
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.
yesno
to string
coalesce
statement.yes
and no
scenarios do generate the correct code ie without NULL.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.