List Of Values

Hi

Could anyone please tell me how we can assign a list of values (either static values or through custom SQL) to a variable (especially dimension) in Looker?

Thanks
Shankar

@shankarr is this what you are looking for?

You might also try playing around with the ‘list’ measure type. It is pretty awesome.

1 Like

Thanks for your inputs. But I guess what I am expecting is something different.
Let me explain you with an example.
Eg.
Scenario 1: If I have a database dimension called Country, I want its list of values in my looker explore as only which starts with the letter ‘A’. How can I achieve this in my LookML?
Scenario 2: I want to create a static list of values to the dimension. How to do this in LookML?

Let me know for any clarifications.

Thanks
Shankar

Hi @shankarr,

If your main aim is to affect the values that a user can filter by then I’d use these options:

For scenario 1 I’d use suggest dimension: https://docs.looker.com/reference/field-params/suggest_dimension
For scenario 2 I’d use suggestions: https://docs.looker.com/reference/field-params/suggestions

If, alternatively, you want to affect which possible values are returned from the dimension field in any query (not just filters), then I’d use the sql param to do something like this:

  dimension: countries {
    type: string
    sql: ${TABLE}.countries ;;
  }
  
  dimension: countries_starting_with_a {
    type: string
    sql: CASE WHEN UPPER(${countries}) LIKE 'A%' THEN ${countries} ELSE NULL END ;;
  }

That will of course return all rows in the table and just NULL out the values that don’t meet the criteria. It won’t exclude those rows that have countries that don’t start in A. For that you’d need a derived table.

Cheers,

Jon

1 Like