I have run into the following situation a bunch of times, and it feels like a best practices recommendation of different approaches would be helpful.
When dealing with the problem described in the post below, and taking the second approach “Exclude the erroring field from explores”, what are the different methods of doing so?
From what I gather, there are a couple of options for doing so:
- Exclude the fields at the explore level
a. Exclude individual fields - fields: [ALL_FIELDS*, -view.field1]
b. Exclude sets - fields: [ALL_FIELDS*, -set1*]
- Exclude the fields at the join level
a. Include individual fields (excluding everything else) - fields: [field1]
b. Include sets and exclude individual fields in those sets - fields: [set1*, -field1]
I know some of the syntax above is incorrect, because my LookML still isn’t validating.
- What is the correct syntax?
- In which cases does the view need to be scoped? Do sets need to be scoped to the view?
- Are there any other options I haven’t covered above?
My specific situation is that I have many fields in a single view that cause errors across many explores, so excluding sets at the explore level (1b, above) seems like the best solution, although I can’t get the syntax right.
@jyau, I think you have this just about right. Let me answer your questions:
What is the correct syntax?
If you’re doing this at the explore level (docs are here), it is:
If you’re doing this at the join level (docs are here), it is:
In which cases does the view need to be scoped? Do sets need to be scoped to the view?
This depends upon whether or not you’re doing this at the explore level or the join level.
At the explore level, if you’re trying to reference a field or set from the explore view (by which I mean
- explore: this_one), you don’t need to scope. But if you’re trying to get at a field or set from a joined view, you do.
At the join level, you never need to scope. Just keep in mind you can only get at fields or sets from the specific joined view you’re working on. In other words:
Are there any other options I haven’t covered above?
I don’t think so, this is the way to exclude fields if that is the approach you want to take. Per that other article that you linked to, you can also try to solve this by joining in additional views to different explores. If you are still getting errors after trying these approaches, feel free to contact chat and go through your specific LookML.
An other option is to move the offending fields to bare joins.
suppose you have
The problem with this is that every time you join orders, you are going to have to join users so the users.count will resolve.
An alternative is to move the joint fields into a bare join. A bare joins is a join with no sql logic. Think of it as just a list of field definitions…
When you include ‘orders.average_user_spend’ in your query, looker will figure out that you need to join users and do the right thing. You are free to use the orders view in other places without the ‘orders_extra’ fields.
You could also use the
extendsparameter to move the offending fields to extended views, and only join that extended view when desired. We have an overview of using