Using a simple case when function not working

Utz

Hi so I am trying to use an if-then statement in beast mode but it is not giving me the correct output.

The field I am using in this code is product status and these are following values inside it.

What I am trying to do is bucket the values into two buckets one which is repeat and one which is new.

The code that I am trying to use is this

CASE WHEN PRODUCTSTATUS = 'R' OR 'Repeat' THEN 'Repeat' ELSE 'New' END

When I use this define function I only get the value for "new" not "repeat" not sure what I am doing wrong but below is the example

I need R and Repeat to come as repeat and anything else to come up as new if anyone can help me with this code.

marcel_luthi

You could try an IN statement instead of OR, something like:

CASE WHEN TRIM(UPPER((`PRODUCTSTATUS`)) IN ('R','REPEAT') THEN 'Repeat' ELSE 'New' END

I'm setting it to upper case and trimming whitespaces just to cover the possibility of the data not being clean, which is what I could guess based on the output you currently show in the screenshot.

GrantSmith

It's treating your 'Repeat' clause in your logical expression as TRUE as it's not 0 value. You need to compare the value again to your field:

CASE WHEN `PRODUCTSTATUS` = 'R' OR `PRODUCTSTATUS` = 'Repeat' THEN 'Repeat' ELSE 'New' END

Utz

Hi Grant,

Thank you for the quick response seems like that did not fix the issue here is a screenshot of the results:

any other variation of the code I can try

marcel_luthi

You could try an IN statement instead of OR, something like:

CASE WHEN TRIM(UPPER((`PRODUCTSTATUS`)) IN ('R','REPEAT') THEN 'Repeat' ELSE 'New' END

I'm setting it to upper case and trimming whitespaces just to cover the possibility of the data not being clean, which is what I could guess based on the output you currently show in the screenshot.