Using Fields Parameters in LookML

(Alejandro Aguilera-Ruiz) #1

The Field Parameter,fields, allows you to specify which fields from an explore are exposed in the Explore UI. The fields parameter can be used both for an explore and for a join. When used in a join, you can control which fields get pulled in with the join. If you do not use fields, Looker defaults to exposing all fields.

How to include a single field

You can include a single field in an Explore like this:

- explore: match_level_facts
  label: "La Liga EspaƱa 2013-2014"
  fields: [match_level_facts.team_name]

This example will only bring in the field team_name in to the explore.

Similarly, you can do this for a join like so:

- join: la_liga_espana_2013_2014_v2
  sql_on: ${la_liga_espana_2013_2014_v2.home_team_id} = ${team_id}
  fields: [la_liga_espana_2013_2014_v2.home_team_name]    

How to exclude a single field

Excluding a field can only be done at the explore level. You must first include all other fields using the ALL_FIELDS* set. Then, exclude a specific field using - in front of the field name:

- explore: match_level_facts
  label: "La Liga EspaƱa 2013-2014"
  fields: [ALL_FIELDS* , -match_level_facts.team_id]

This example will include all fields except team_id.

Continue reading to see how to exclude fields in a join.

Including/Excluding multiple fields

I have found that the best way to reach this result is by first creating custom sets in the specific view file:

sets:

home_detail: 
  - home_team_id
  - home_team
  - ft_home_goals
  - ht_home_goals
  - home_shots
  - home_shots_on_target
  - home_fouls
  - home_corners
  - home_yellow_cards
  - home_red_cards
  - total_ft_home_goals
  - total_ht_home_goals
  - total_home_shots
  - total_home_shots_on_target
  - total_home_fouls
  
away_detail: 
  - away_team_id
  - away_team
  - ft_away_goals
  - ht_away_goals
  - away_shots
  - away_shots_on_target
  - away_fouls
  - away_yellow_cards
  - away_red_cards
  - total_ft_away_goals
  - total_ht_away_goals
  - total_away_shots
  - total_away_shots_on_target

match_detail:
  - date
  - matchup
  - ft_result
  - ht_result

One then uses these sets to batch include/exclude multiple fields into an explore or join

- explore: match_level_facts
  label: "La Liga EspaƱa 2013-2014"
  fields: [match_level_facts.home_detail* , match_level_facts.away_detail*]

Note: Make sure to use the asterisx!

- join: la_liga_espana_2013_2014_v2
  sql_on: ${la_liga_espana_2013_2014_v2.home_team_id} = ${team_id}
  fields: [la_liga_espana_2013_2014_v2.match_detail*]

Through using sets:, you will only including the specific fields declared in each set.

Have fun!

Cheers,

Alejandro aka: Freedom Looker

3 Likes

(Carter Moar) #2

A+ :apple:

Another cool thing is that if you exclude a field that is needed for another field, Looker is smart enough to not create unknown field substitution or unknown or inaccessible field errors (at least the last time I tested this).

2 Likes

(Lucas Thelosen) #3

Great feature and write up, Alejandro!

I have used it a bunch of times now since I read your article. The ability to quickly exclude sets of fields (such as users.privat_information) keeps the LookML code clean and helps to prevent an essential field that was supposed to be excluded.

Carter, you are right. Just because you exclude a field (or use the syntax fields: [] in a join) does not mean the field becomes inaccessible for other measures. For example, if user_id was part of the exclusion, the measure Count of Users with the SQL of count(distinct user_id) would still work.

One thing to keep in mind: Right now (3.26), this only works on the explore level. If the set of fields you want to exclude is on one of your joins, then you have to specify the set you are excluding on the explore level, not the join level. On the join level, you either list out all the fields you want to include (and leave out the ones you want to exclude, or you donā€™t specify the field (including all of them) and then exclude them on the explore level.

2 Likes