Multiple case statements

NFerna · April 9

I have 3 columns in a table with with fruit name , benchmark value and achieved benchmark value.

Ideally I would like to use colors on a table when (in this example) :

Apple is equal or greater than Apple benchmark value then Green, else Red
Orange is equal or greater than Orange benchmark value then Green, else Red
Pear is equal or greater than Pear benchmark value then Green, else Red

Name	Benchmark Value	Achieved Value
Apple	3	4
Orange	5	3
Pear	2	6

Firstly, is this possible using the colors chart properties conditional functions? Secondly, can these be combined in a case statement?

The limitations here are that each fruit has it's own benchmark value, so I am having trouble formulating this into a beastmode as it keeps throwing a syntax error:

CASE (WHEN Fruit= 'Apple' and Achieved Value >= 3 )

OR

(WHEN Fruit= 'Orange' and Achieved Value >= 5 )

OR

(WHEN Fruit= 'Pear' and Achieved Value >= 2 )

THEN 'PASS' ELSE 'FAIL')

END

RobSomers · April 9

@NFerna For the beast mode part of your question, it would be something like this:

CASE WHEN 'Achieved Value' >= 'Benchmark Value' THEN 'PASS' ELSE 'FAIL' END

Assuming your data is set up so that it's Fruit, Achieved Value, Benchmark Value, this will go through and for each fruit/row and compare Achieved Value and Benchmark Value.

For your color coding question, to color individual cells, you would need to use an HTML table and some HTML code, but if you were fine highlighting the entire row red or green, you can use the beast mode above and add it to your table, then hide it. Then set up your color properties so that if the value is 'Pass' then it highlights the row green and if it's 'Fail', then highlight the row red.

MichelleH · April 9

EDIT - @RobSomers' suggestion will work best for this case since the Benchmark Value and Name are columns in the dataset. I will keep my comment below for reference on the syntax in multi-condition case statements.

@NFerna Your thought process is correct, but you just need a few adjustments to your syntax. You are likely getting syntax errors because of an unequal number of open and closed parenthesis. In this situation, you also do not need to separate conditions by both WHEN and OR since they achieve the same result.

Option 1 (below) demonstrates how to accomplish this using WHEN, though note that each "WHEN" condition must be followed by a "THEN" output value. Option 2 combines all conditions into a single "WHEN" condition, grouped by parentheses and separated by OR. Both accomplish the same result, but I personally prefer to Option 1 because I find it easier to configure conditions and outputs independently, but Option 2 can also be useful when the conditions are inter-related.

Option 1:

CASE 
  WHEN Fruit= 'Apple' and Achieved Value >= 3 then 'PASS'
  WHEN Fruit= 'Orange' and Achieved Value >= 5 then 'PASS'
  WHEN Fruit= 'Pear' and Achieved Value >= 2 THEN 'PASS' 
  ELSE 'FAIL'
END

Option 2:

CASE 
  WHEN (Fruit= 'Apple' and Achieved Value >= 3) 
    OR (Fruit= 'Orange' and Achieved Value >= 5) 
    OR (Fruit= 'Pear' and Achieved Value >= 2) 
    THEN 'PASS' 
  ELSE 'FAIL'
END

NFerna · April 10

Hi everyone,

I have gone with option 1 beastmode which has fixed the problem - thank you! This does however present an issue brought on by a new column. We now have the below:

Name	Variety	Benchmark Value	Achieved Value
Apple	Red Apple Batch 23	3	4
Apple	Red Apple Batch 22	4	4
Orange	Batch 11 v1	3	4
Orange	Batch 11 v23	5	3
Pear	Batch 20 TVS	2	6
Pear	Batch 24 LKT	4	1

I am looking to utilize the LIKE function but once again having trouble whilst the beast mode is saving without issue, the ruleset is not pulling through the correct result (i am seeing FAIL for values that should have PASS:

CASE
WHEN Fruit= 'Apple' and Achieved Value >= 3 then 'PASS'
WHEN Fruit= 'Orange' and Achieved Value >= 5 then 'PASS'
WHEN Fruit= 'Pear' and Variety LIKE 'TVS' and Achieved Value >= 2 THEN 'PASS'
ELSE 'FAIL'
END

So for line 5 in this case, I am still seeing FAIL for Pear variety TVS, even though it should be a pass.

Any help greatly appreciated.

MichelleH · April 10

@NFerna When using the LIKE operator, be sure to use the % wildcard so that it matches values that that contain the letters "TVS" but don't match exactly. It should look like Variety LIKE '%TVS%'

Multiple case statements

Answers

Categories