How to group options within a dimension?

Question

I have a dimension called 'group':

Within 'group' dimension, there are drop down options for 'group A', 'group B', 'group C', 'group D', 'group E' and 'group E-1'.

I would like to groupby(or group) drop down options 'group E' and 'group E-1' can call it 'group X'. So in my drop down I get 'group X' as an option.

How can I do this?

Answer 1

Something like this should work for you.

dimension: group {
type: string
sql:   CASE 
        WHEN ${TABLE}.group IN ('Group E','Group E-1') THEN 'Group X'
        ELSE ${TABLE}.group
       END ;; 
}