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

moebe
Participant V

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?
0 12 1,313
12 REPLIES 12

mprogano
Participant III

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

moebe
Participant V

Sounds interesting
What syntax do you have in mind?

moebe
Participant V

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?

PatKearns
Participant I

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 ’

mprogano
Participant III

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

mprogano
Participant III

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

moebe
Participant V

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 🙂

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)

moebe
Participant V

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!

moebe
Participant V

@fabio1
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?

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.

moebe
Participant V

The style guide is amazing, thanks

Top Labels in this Space
Top Solution Authors