Conditional references to views that may not be joined?

done
low_priority
reply

(Rob Schoenbeck) #1

Is there a way to conditionally reference views that may not be joined into the referencing view in question? I’ve tried to do this using liquid variables that check if a certain base view for an explore is present, but still get errors about the views not being joined, even though they technically shouldn’t be referenced due to the conditional logic around the liquid variables.

The core problem is that I’m creating dimensions in a single view that refer to the same essential view, but which are differently aliased in different Explores both for clarity and when multiple copies of the views may be attached to different entities (ex: a single “account” view underlies both a lender account and a repayment account in the same Explore, for example).

I can just repeat the same dimension multiple times for each alias and then use field sets for each appropriate Explore, but that seems very repetitive and unnecessary.

Here’s a fake example illustrating the problem:

   dimension: my_user_segment_flag {
       type: yesno
       sql: case when {% if my_base_view._in_query %} 
           ${joined_to_base_view.segment}
       {% else %} 
           ${joined_to_other_explore.segment} 
       {% endif %} = 'interesting_segment' 
        then true else false end ;;
    }

So the view defining this dimension might be joined to both the explore around my_base_view and maybe a couple other explores that join in joined_to_other_explore. But Looker will generate an error about missing views, since only one of the views will be actually joined to a given Explore.

Is there another way to conditionally change the view referenced in the dimension based on what explore it might be joined to?


(Nicholas Wong) #2

Are you finding ways you can join the tables without having to declare the variables in every dimension you create? (For example like the my_user_segment_flag dimension)

Looker allows you to declare joins at the start of the code. With development mode on, if you head over to Develop -> Project, you can declare your joins at the start of the code.

explore: order {
join: customer {
from: person
sql_on: ${order.customer_id} = ${customer.id} ;;
}
join: representative {
from: person
sql_on: ${order.representative_id} = ${representative.id} ;;
}
}

This is an example. For more information about joins, you can check out https://docs.looker.com/reference/explore-params/join.


(Izzy) #3

The reason this happens is due to the order in which we parse liquid and lookml. Liquid is evaluated after we’ve run over the full LookML and pulled in all the dependencies.

Check out me and @tnebesar’s convo here: Liquid variable in_query using fields from within logic and thus eliminating use case

In a nutshell-- Because you’re referencing ${joined_to_base_view.segment}, no matter WHAT that if statement evaluates to, Looker will decide it requires that field and if it can’t find it, then you’ve got an error.

You can get around this by referencing the actual SQL table reference without the ${ } lookML substitution syntax.

Try

   dimension: my_user_segment_flag {
       type: yesno
       sql: case when {% if my_base_view._in_query %} 
          whatever_joined_to_base_view_is_actually_called_in_the_db.segment
       {% else %} 
           whatever_joined_to_other_explore_view_is_actually_called_in_the_db.segment
       {% endif %} = 'interesting_segment' 
        then true else false end ;;
    }

But, as I caution in the other thread, you’re sidestepping a lot of the value of LookML there— And you gotta remember that you’re doing that or it might cause even more confusion.


(Rob Schoenbeck) #4

Thanks Izzy – that’s just what I was looking for. Looks like it will also require adding “always_join” to the Explores for tables so referenced.

You’re right that all that adds some extra complexity and tradeoffs that aren’t really worth it from my perspective – I’m working with a very particular case where cumulative historical lifetime user metrics need to be connected to multiple Explores covering different transaction types in a date-relative way (and solving that by creating a daily per-user table isn’t really feasible from a performance perspective).

So I think for now at least I’ll just keep my hack solution of repeating the same 3 dimension definitions 3+ times each in this view, for each alias of a Looker view it expects to encounter, since it doesn’t seem like there are significantly better alternatives in Looker at the moment.


(Ian) #5

We put in a dirty hack to get around this.
We have our liquid which joins everything it sees without doing the condition check in liquid but we fudge the join condition also based on liquid so we don’t pay the price for the join. Results in 1=1 if we actually want the data from the table or 1=2 if we don’t.
Looks like you are doing something slightly different but join in all needed views in explore and put liquid in join condition as well.