Question

OR Conditions in Looker - a nightmare or am I just thinking wrong


Userlevel 4

The following problem:

I have a department. This department is responsible for several products.

Now I have to make a filter that should look like this in the sql:

where (company_department = ‘Department Name’ or product_owner = ‘Department Name’)

This has to be done in LookML, because this logic has to be valid for every Looker of this exploration and the users shouldn’t/can’t work with custom_filter.

At first I thought that this could not be that hard, but I can’t find a solution.



  • Parameters do not work because the departments are defined regularly in the source system and we would not even be able to maintain this definition in our system.

  • Templated filters don’t work either, because I can’t create an OR condition with them.

    Is there really no standard approach to solve the problem properly.

    Is looker really not able to handle OR conditions?


12 replies

Userlevel 3

Why not just created a unified dimension using a CASE WHEN?

Userlevel 4

Sounds interesting

What syntax do you have in mind?

Userlevel 4

Short update:


I still haven’t found a good solution.


I have tried the following.



  1. parameter: does not work, because one parameter does not allow more than one value

  2. templated filter: doesn’t work, because I don’t reach an OR condition with it but:

    and department = “A” and (department = “A” or activity owner = “A”) what is wrong

  3. PDT: I have created a PDT that gives me all departments. I use this as a joined table

    select * from fact inner join dimension on (x=x)

    inner join pdt on (1=1)

    where (pdt.department = dim.department or pdt.department = dim.activity_owner).


That works. With a single valuation day even tolerable, with several days the performance goes through the cross join into the cellar 🙂.


Also a direct join is not better, but rather worse:

select * from fact inner join dimension on (x=x)

inner join pdt on (pdt.department = dim.department or pdt.department = dim.activity_owner)


I find this very frustrating, because the logic is really not complex.

Unfortunately there is no way (at least I don’t know any) to insert a value directly into the SQL statement except parameters.


Do any of you have any ideas how I could solve this better?

I believe what @mprogano was suggesting would work. Create a new dimension:


dimension: department_filter {
type: yesno
sql: case
when ${company_department} = 'Department Name' or ${product_owner} = 'Department Name' then true else false end;;
}

or probably even more concise:


dimension: department_filter {
type: yesno
sql: (${company_department} = 'Department Name' or ${product_owner} = 'Department Name') ;;
}

Edit: swapped ` for ’

Userlevel 3

I also learned you can do this all in LookML if you wanted to…


Userlevel 3

@moebe if you do run the above code (which is great), maybe sure you swap out the the and for a single quote (aka ')

Userlevel 4

Maybe I’m just totally on the wrong track.

The @PatKearns proposal

dimension: department_filter {

type: yesno

sql: (${company_department} = ‘Department Name’ or ${product_owner} = ‘Department Name’) ;;

}

requires that I know the “department name”.

But I do not. It’s just one department among many.

So the suggestion only works if I am willing (like with a parameter) to maintain all possible departments manually.

But this would really only be a stopgap solution, because I always run the risk of delivering incorrect dashboards to my customers until I notice the presence of a new department in the source system


The same applies to the proposal: @mprogano




would surely work, but only with manual mapping on my part.

As I said, the easiest solution would be to parse the result of a filter directly into the SQL, but that doesn’t seem to work.


But thanks a lot to both of you. At least I have another solution. And having more options never hurts 🙂

Userlevel 7
Badge

Little known trick: if you use a filter-type field, you can define a very custom SQL for it that will be used in the where clause.


filter: department {
type: string
suggest_dimension: company_department
sql:
{% condition department %} ${company_department} {% endcondition %}
OR {% condition department %} ${product_owner} {% endcondition %}
;;
}

(The department inside of the {% condition %} tag may be optional when a field is referring to it’s own filter selection, I forget, but you can test. In any case, this is the general form of the tag that can refer to another filter if necessary)

Userlevel 4

This is great!

Thanks a lot.

Now the suggest_dimension has to work (I’m probably doing something wrong) and then the problem is solved.

Thanks!

Userlevel 4

@fabio

I have one more question.

Here in this example I create a new dimension based on fields from two different views.

I have this again and again and think each time in which view this dimension should be created.

Therefore I always consider to outsource such things.

This should be possible via refinements, right?


At the moment I have this:



  1. 1:1 views from the base table of the database (as they are automatically generated by Looker)

  2. hide unneeded information, labeling, description, measures etc. via refinement

  3. refinement for very specific additional logic.


Now I consider to outsource parameters, filters and artificial dimensions (with Concat etc.).


What do you think about this?

Userlevel 7
Badge

It’s a stylistic preference, but yes, I like that approach to using refinements. It sounds like you may have read my “LookML layering” article.


For defining fields that reference across two views, I like to use separate field-only views, as described in my style guide - expand rule F1 to see an example.


One thing to be aware of - if you plan on using the LAMS linter to enforce the style guide, unfortunately it does not yet work with refinements as they are a new feature. But, all of the style guide rules are very doable by hand, as it was originally a stand-alone style guide before the linter exited.

Userlevel 4

The style guide is amazing, thanks

Reply