Liquid variable in_query using fields from within logic and thus eliminating use case

done
liquid
normal_priority
reply

#1

Hi,

I’m trying to create sales reporting that allows users to pull sales this year (TY) and compare to sales last year (LY), the year before last (LLY), planned sales and forecasted sales as desired.

Because for example, there can be sales for items, categories, etc. LY that didn’t sell TY, the joins need to be full joins, and we need to coalesce dimensions depending on what users want to compare. For example, if pulling TY vs. LY, the item dimension needs to be coalesce(ty.item,ly.item), but if only pulling TY sales, the item dimension can just be ty.item.

I tried to accomplish this using the in_query liquid variable. However, Looker seems to be think that LY and LLY are “in the query” because they’re reference in the item dimension coalesce liquid logic. I think the in_query logic should ignore what’s “within” the liquid logic.

See below for the liquid logic that I tried and queries showing my desired SQL and the SQL that I got instead. I tried 2 versions of the dimension, and got coalesce(ty.item,ly.item,lly.item) from both.

Thanks,
Tim

Tried 2 versions of coalesces:

  dimension: item_coalesce_v1 {
    sql:
      coalesce(
       ${item}
       ,{% if sales_ly._in_query %} ${sales_ly.item} {% else %} null {% endif %}
       ,{% if sales_lly._in_query %} ${sales_lly.item} {% else %} null {% endif %}
      )
      ;;
  }
  
  dimension: item_coalesce_v2 {
    sql:
    {% if sales_ly._in_query and sales_lly._in_query %}
      coalesce(${item},${sales_ly.item},${sales_lly.item})
    {% elsif sales_ly._in_query %}
      coalesce(${item},${sales_ly.item})
    {% else %} 
      ${item} 
    {% endif %}
    ;;
  }

Desired SQL: (first version of coalesce)

select
	coalesce(ty.item,null,null) as item
	,sum(ty.sales) as ty_sales
from sales_ty ty

Actual SQL:

select
	coalesce(ty.item,ly.item,lly.item) as item
	,sum(ty.sales) as ty_sales
from sales_ty ty
full outer join sales_ly ly
	on ty.item = ly.item
full outer join sales_lly lly
	on ty.item = lly.item

(Izzy) #3

This one was a bit of a brain teaser the first time I saw it, too. It all kind of comes down to order of operations: LookML is processed before liquid is, and when you use LookML substitution syntax (${sales_ly.item}), that statement and all of it’s references/dependencies are processed fully before the liquid if has a chance to evaluate. That means that if you use subsitution syntax referencing a different view anywhere in the sql: parameter, even in liquid that you’ve commented out, it will still bring in the relevant joins since that happens earlier.

This is actually intended behavior, since it’s a requirement of a lot of other important functions that lookML evaluate fully before liquid. One way to get around this, although you have to be really careful with it since it can get kind of fragile, is to just remove the substitution syntax and use direct database references.

dimension: item_coalesce_v2 {
    sql:
    {% if sales_ly._in_query and sales_lly._in_query %}
      coalesce(table.item,sales_ly_table_name.item,sales_lly_table_name.item)
    

etc
etc
  }

The downside is that you don’t get Looker doing all of the good automatic joining it usually does, so just be careful and make sure it works as planned.


#4

Ahh this works! Thanks a lot Izzy.