Solved: Creating a Unique list of values using a custom di... - Page 2

Jester113 · 06-24-2022 01:41 PM

Hello

Im brand new to Looker and I am trying to learn as I go. Here is the situation. I created a custom dimension that lists out company names based on some nested if statements. It worked but this is what I got:

Company	Total Employees
Company 1	100
Company 2	500
company 2	1000
Company 3	6000
ComPany 3	500

I noticed that Company 2/company 2 and Company 3/ComPany 3 are the same, but it was treated as 2 different entities because of the casing. How can you merge duplicates with different casing into 1 so it can look like this instead?

Company	Total Employees
Company 1	100
Company 2	1500
Company 3	6500

Is it possible to do in a dimension? I saw you can do it with a table calculation with the offset function, but if i do that it still won’t group the values. Is it also possible to do it without using LookML as I was instructed not to touch LookML since it will affect everyone in the company and not just my 1 report.

Any advice and guidance will be very much appreciated. THank you!

vikram1

@Jester113 - You can make changes in the sql parameter of dimension. I am not sure which SQL dialect are you working on, however, you can try the lower or upper function of SQL.

  dimension: company {
    type: string
    sql: lower(${TABLE}.company) ;;
  }

all letter of column change to lower casing and only three records will produce in the resultset.

View solution in original post

Creating a Unique list of values using a custom dimension