9 Replies Latest reply on Apr 30, 2018 12:06 PM by Okechukwu Ossai

# Help with a Case State,emt

What am I doing wrong in the following Case Statement?

CASE IFNULL([COSTS],0) THEN '*'

WHEN [COSTS] < 1    THEN 'Red'

WHEN [COSTS] >1     AND [COSTS] <= 2.79 THEN 'Yellow'

WHEN [COSTS] >2.79  AND [COSTS] <= 3.79 THEN 'Bronze'

WHEN [COSTS] >3.79  AND [COSTS] <= 3.79 THEN 'Silver'

ELSE 'Gold'

END

Thanks,

K

• ###### 1. Re: Help with a Case State,emt

Hi Karen,

You need to use IF statement instead of CASE. I'm not sure what the first line of your code is trying to achieve. However, something along this line may work for you. You may modify the first line to suit your need.

IF ZN([COSTS]) = 0 THEN '*'

ELSEIF [COSTS] <= 1 THEN 'Red'

ELSEIF [COSTS] >1 AND [COSTS] <= 2.79 THEN 'Yellow'

ELSEIF [COSTS] >2.79 AND [COSTS] <= 3.79 THEN 'Bronze'

ELSEIF [COSTS] >3.79 AND [COSTS] <= 3.79 THEN 'Silver'

ELSE 'Gold'

END

Depending on your data structure, you may replace [COSTS] with SUM([COSTS]) to get the correct answer.

Hope this helps.

Ossai

• ###### 2. Re: Help with a Case State,emt

Thanks that did the trick.

K

1 of 1 people found this helpful
• ###### 3. Re: Help with a Case State,emt

You're welcome Karen. Glad it helped.

Ossai

• ###### 4. Re: Help with a Case State,emt

It seems you marked your own response as the correct answer instead. I know this can be a little fiddly, took me some time to get used to it.

• ###### 5. Re: Help with a Case State,emt

Sorry, I am new to this forum, how do I rectify this?

K

• ###### 6. Re: Help with a Case State,emt

I need further help with Case statement.  I need to check value in gpa_category (text), (C, M, S, Q, T) and give it a value of Cost, Quality, etc.

case [gpa_category] = 'C' then 'Cost'

when [gpa_category] = 'M' then 'Management'

etc.

end

Why doesn't this work, not sure if this should be a case or if statement?

K

• ###### 7. Re: Help with a Case State,emt

Hi Karen,

Try this:

CASE [gpa_category]

WHEN 'C' THEN 'Cost'

WHEN 'M' THEN 'Management"

etc.

etc.

etc.

END

Hope it helps.  Thx, Don

• ###### 8. Re: Help with a Case State,emt

What is wrong with this statement, is this issue due to mixing string with # (integer).

I am new to this forum, how do I copy and paste the code into this comment?

• ###### 9. Re: Help with a Case State,emt

Hi Karen,

You can just copy it and paste directly into the comment box.

It may be better to start a new discussion and ask the question there. This is because the original question for this thread has been answered. Starting a new thread for the additional question will give your new question more visibility in the community.

Ossai