I would like to achieve the following in looker SQL (hence the $):

• If my count ${count_students} > 0 and the ${subject} is NOT ('Biology') then the cost of the class should be provided.
  • ...however if my count ${count_students} = 0 and the ${subject} is NOT ('Biology') then the cost of the class should be divided by 2.
• Secondly, if the results of the case when statement is > $100.00, I'd like the value to be capped at $100.00.

I'm getting an error message because I'm probably not using this two condition CASE WHEN statement correctly and probably also because I'm not wrapping it into the if statement correctly.

IF(
    (
        CASE
            WHEN ${count_students} > 0 AND ${subject} NOT IN ('Biology') THEN ( ${costs_of_class} )
            WHEN ${count_students} = 0 AND ${subject} NOT IN ('Biology') THEN ( ${costs_of_class} / 2 ) END
    ) > 100,
    100,
      0
)

CodePudding user response：

So your existing SQL is like this:

SELECT 
    count_students
    ,subject
    ,costs_of_class
    ,CASE
        WHEN count_students > 0 AND subject NOT IN ('Biology') THEN costs_of_class 
        WHEN count_students = 0 AND subject NOT IN ('Biology') THEN costs_of_class / 2 
    END as case_val
    ,iff(case_val > 100, 100, 0) as final_val
from values
(0, 'Biology', 20),
(0, 'NOT_Biology', 30),
(1, 'Biology', 50),
(1, 'NOT_Biology', 70)
t(count_students, subject, costs_of_class)

which gives:

what you are wanting is LEAST so you don't have to use the SQL to repeat the CASE logic:

SELECT 
    count_students
    ,subject
    ,costs_of_class
    ,CASE
        WHEN count_students > 0 AND subject NOT IN ('Biology') THEN costs_of_class 
        WHEN count_students = 0 AND subject NOT IN ('Biology') THEN costs_of_class / 2 
        ELSE -1 -- what value do you want to use here??
    END as case_val
    ,least(case_val, 100) as final_val
from values
(0, 'Biology', 20),
(0, 'NOT_Biology', 30),
(1, 'Biology', 50),
(1, 'NOT_Biology', 70),
(0, 'Biology', 220),
(0, 'NOT_Biology', 230),
(1, 'Biology', 250),
(1, 'NOT_Biology', 270)
t(count_students, subject, costs_of_class)

giving:

As long as you solve for the default case and what you want to do with Biology classes.

Thus in your form:

LEAST( 
    CASE
        WHEN ${count_students} > 0 AND ${subject} NOT IN ('Biology') THEN ( ${costs_of_class} )
        WHEN ${count_students} = 0 AND ${subject} NOT IN ('Biology') THEN ( ${costs_of_class} / 2 ) 
    END,
    100)

CodePudding user response：

The code was almost correct, the conditional function is called IFF:

Single-level if-then-else expression. Similar to CASE, but only allows a single condition.

IFF(
  (CASE
    WHEN ${count_students} > 0 AND ${subject} NOT IN ('Biology') THEN (${costs_of_class})
    WHEN ${count_students} = 0 AND ${subject} NOT IN ('Biology') THEN (${costs_of_class}/2) 
  END) > 100,
  100,
  0
)