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
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"
}
}