Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

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

  • 11 January 2019
  • 10 replies
  • 3107 views
T

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

10 replies

izzy
Userlevel 7
Badge +1

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.

T

Ahh this works! Thanks a lot Izzy.

T

Hey @izzy


Using my examples from above, I now want to create “dynamic measures” that will either pull LY or LLY sales depending on a parameter filter. See below for Look ML.


If I try to create a dynamic measure with substitution syntax, Looker joins both LY and LLY even though I only want either LY or LLY depending on the parameter filter. If I try without substitution syntax, Looker doesn’t know to join LY or LLY.


Any ideas on how I could achieve these dynamic measures without creating unnecessary joins? Thanks as always for the help


-Tim



parameter: dynamic_measure_filter {

allowed_value: { value: “LY” }

allowed_value: { value: “LLY” }


measure: dynamic_sales_measure {

type: number

sql:

{% if dynamic_measure_filter._parameter_value == “‘LY’” %}

${sales_ly.sales}

{% elsif dynamic_measure_filter._parameter_value == “‘LLY’” %}

${sales_lly.sales}

{% else %}

NULL

{% endif %}) ;;

}


izzy
Userlevel 7
Badge +1

Yeah, that’s the risk of doing this. You lose all of the good parts of the substitution syntax auto-adding necessary fields 🙂.


Without spending too much time on this, and if this really is just a one-off scenario, I did get this working one way or the other. But, it involves moving even farther away from the nice governance benefits of LookML

zz


Hopefully, someone comes up with a way to do this without getting so manual, since this is pretty fragile. You might even want to put it in it’s own explore?


Anyways, here’s the code I have

in the view:


    measure: dynamic_sales_measure {

      type: number

      sql:

      {% if dynamic_measure_filter._parameter_value == "ly" %}

      user.id

      {% elsif dynamic_measure_filter._parameter_value == "lly" %}

      grade.id

      {% else %}

      NULL

      {% endif %} ;;

    }

in the model:


explore: ascent {

  always_join: [user]

  join: user {

    sql:

    {% if ascent.dynamic_measure_filter._parameter_value == "ly" %}

    LEFT JOIN user ON user.id = ascent.user_id

    {% elsif ascent.dynamic_measure_filter._parameter_value == "lly" %}

    LEFT JOIN grade on grade.id = ascent.grade_id

    {% else %}

     

    {% endif %};;

    relationship: many_to_one

  }

Basically, using liquid in the sql: parameter instead of the sql_on: parameter to say “if ly is selected, join in the ly table, else do the other table” and adding an always_join so that it will always be included.


It’s janky + fragile but it does achieve the intended functionality. I think you could make it more robust by making it it’s own explore or at least its own join, maybe via an extended view?

izzy
Userlevel 7
Badge +1

If you do end up building something like that out and making it more sturdy, post what you did!

T

Success! Very clever - thanks a lot for the help. I just had to create a dummy view with nothing in it because my LY view (user in your example) was already joined in.



view: dummy_dynamic_measures {

}


izzy
Userlevel 7
Badge +1

Glad it worked! I just have this nagging feeling that there must be a better way…


At a minimum, it would be super helpful to add a comment line in there saying that it’s tied to x lines in the model that do so-and-so to save someone the headache of figuring it out later on.

T

So it’s working perfectly at the moment. However, the views that I’m joining are PDTs so I believe this will break when the PDTs rebuilt and the hash values in the PDT names change. Is that correct? Any way around?


Here’s how my join looks using your example (just added LR_hash_view as view):



join: dummy_dynamic_measures {

sql:

{% if ascent.dynamic_measure_filter._parameter_value == “ly” %}

LEFT JOIN LR_2G3MKXY5DVKLXPWSKHH4E_user as user on user.id = ascent.user_id

{% elsif ascent.dynamic_measure_filter._parameter_value == “lly” %}

LEFT JOIN LR_2GE111TIZMZ93LC8S91ZF_grade as grade on grade.id = ascent.grade_id

{% else %}

{% endif %};;

relationship: many_to_one

}


izzy
Userlevel 7
Badge +1

You can use ${user.SQL_TABLE_NAME} or ${grade.SQL_TABLE_NAME} (literally that, you have to write SQL_TABLE_NAME) to automagically fetch the latest hash and insert it. See, substitution syntax IS useful! 😁


Docs here: https://docs.looker.com/data-modeling/learning-lookml/derived-tables#referencing_derived_tables_in_other_derived_tables

T

Ahh I thought I’d go back to the issue where Looker evaluates the substitution syntax first and creates all of the joins. I guess not though since this is in the join and is a table rather than a field…should’ve tested first. Thanks again!

Reply


Terms of Service