Solved

Creating a Unique list of values using a custom dimension

  • 24 June 2022
  • 3 replies
  • 1825 views

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!

icon

Best answer by vikram.singh-1632924966 27 June 2022, 19:37

View original

3 replies

@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. 

@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. 

Hi Vikram. I may have posted this in the wrong channel since I’m not allowed to use LookML, which i am assuming the code snipped you provided in manipulated in LookML right? 

 

Follow up question though, I was told that if I make changes in LookML that will not just affect 1 report but every single report in the company. Is there a way to just change the LookML for my Look or any changes will really affect the entire company? THank you in advance for the help and thank you for your response! It is very much appreciated!

Userlevel 7
Badge +1

Create Custom Dimension in your Look and use the function

Reply