Question

grouping number and string data types into one dimension

  • 27 August 2021
  • 2 replies
  • 106 views

I have two different data types in a dimension , number and range under the dimension  for employee size:

e.g. 1,2,3,4…..and  2-7,8-19,20-39,40-99,100+   

 

I need to create a custom dimension for employee size ranges:

1

2-7

8-19

20-39

40-99

100+

and unknown. 

 

How can this be done either in custom dimension or in LookML?


This topic has been closed for comments

2 replies

I think the easiest way (as long as the groups are stagnant) is a case statement that works like this:

 

case when employee_size ilike ‘%-%’ then employee_size

          when try_to_decimal(employee_size) = 1 then ‘1’

          when try_to_decimal(employee_size) between 2 and 7 then ‘2-7’

          etc.

             else null end as custom_employee_size_dimension

 

Using whatever functions your warehouse will accept of course.

 

If they aren’t stagnant then I can give you a few more ideas.

Thanks Lizzi 

I was able to successfully do this with below statement:

CASE WHEN ${TABLE}.employees like "%-%" then CAST(SPLIT(${TABLE}.employees, "-")[SAFE_OFFSET(1)] AS INT64 )
    WHEN ${TABLE}.employees like "%+" then CAST(SPLIT(${TABLE}.employees, "+")[SAFE_OFFSET(0)] AS INT64 )
ELSE CAST(${TABLE}.employees  AS INT64 )
END