How can we define a dimension based on another dimension?

[Rwb]

I get this error:

The Amazon Redshift database encountered an error while running this query.
ERROR: column “department_bucket” does not exist in applications, stg_applications_jobs, unnamed_join, stg_jobs, unnamed_join, stg_departments, unnamed_join

when I try to create a new “org_bucket” dimension based on my existing “department_bucket” dimension:

dimension: department_bucket {
    case: {
      # a bunch of other cases here
      when: {
        sql: ${TABLE}.department_name LIKE '%IT%';;
        label: "IT"
      }
      when: {
        sql: ${TABLE}.department_name LIKE '%Design%';;
        label: "Design"
      }
      else: "Non-Better"
    }
  }
  
  
dimension: org_bucket {
case: {
   # other cases here
  when: {
	sql: department_bucket = "Corp";;
	label: "Corp"
  }
  when: {
	sql: department_bucket IN ("Eng", "IT", "Product", "Design");;
	label: "Tech"
  }
  else: "Else"
}
}

How can we define a dimension based on another dimension? Thanks.

P.S. If I prepend “${TABLE}.” to “department_bucket”, then the error becomes:

The Amazon Redshift database encountered an error while running this query.
ERROR: column stg_departments.department_bucket does not exist

[Rwb]

I think my problems were that I was using double-quotes instead of single-quotes and didn’t surround department_bucket with ${}.

This seems to work:

  dimension: org_bucket {
    case: {
      # other cases
      when: {
        sql: ${department_bucket} = 'Corp';;
        label: "Corp"
      }
      when: {
        sql: ${department_bucket} IN ('Eng', 'IT', 'Product', 'Design');;
        label: "Tech"
      }

      else: "Else"
    }
  }