Question

Use dimension when creating measure with Case statement

  • 5 January 2022
  • 1 reply
  • 26 views

I am trying to create a measure conditionally based on a dimension.

 

My dimensions:

dimension_group: date {
hidden: yes
type: time
timeframes: [
raw,
date,
week,
month,
quarter,
year
]
convert_tz: no
datatype: date
sql: ${TABLE}.date ;;
}
dimension: status {
type: string
sql: CASE
WHEN UPPER(${TABLE}.status) ='APPROVED' THEN 'Approved'
WHEN UPPER(${TABLE}.status) ='PENDING' THEN 'Pending'
END;;
}

My Measures:

measure: xyz {    
type: sum
value_format: "$#,##0.00"
sql: ${TABLE}.xyz ;;
}
measure: abc {    
type: sum
value_format: "$#,##0.00"
sql: ${TABLE}.abc ;;
}

Measure with conditions:

measure: conditional {
type: number
value_format: "$#,##0.00"
sql: CASE WHEN ${status} = 'Pending' THEN ${xyz}
ELSE ${abc}
END;;
}

 

On my Explore, when I select `date` and `conditional`.  I keep getting the error:

ERROR: column "table.status" must appear in the GROUP BY clause or be used in an aggregate function

 

I understand what the error is. I am just not sure how to fix this. How do I resolve this error? I need all the dimensions and measures.


1 reply

Userlevel 6
Badge +1

I would change the references from LookML ones to native SQL 

measure: conditional {
type: sum
value_format: "$#,##0.00"
sql: IF(${status} = 'Pending', ${TABLE}.xyz, ${TABLE}.abc) ;;
}

 

Reply