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?
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