Using a simple case when function not working

Options
Utz
Utz Member
in SQL DataFlows

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.

image.png

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

image.png

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.

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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.

Answers

  • GrantSmith
    GrantSmith Coach

    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
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Utz
    Utz Member

    Hi Grant,

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

    image.png

    any other variation of the code I can try

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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.

Categories