Combining Multiple Flags Using Concat() and case stmt

larryjacobs
larryjacobs Member
in Beast Mode

In my dataset, I have a multitude of different holds, and if more than one hold applies to the given order number, I want to have them all listed in this 'Hold Name' column i've created using a beast mode. However, when I start adding multiple case statements, the hold name column turns blank. How would I approach getting all of these different holds put into one column?

image.png
image.png
image.png
image.png

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    edited July 2023 Answer ✓

    Hi Larry,

    It will work when the conditions are met, when they are not it will be completely null.

    The solution here is to account for when the holds do not = 'Y' by adding ELSE to your case statement:

    CONCAT(
    CASE WHEN New Logo Hold = 'Y' THEN 'New Logo Hold' ELSE '' END,
    CASE WHEN My Product Customization Hold = 'Y' THEN ', My Product Customization Hold' ELSE '' END
    )

    This is working for me and I believe giving the results you are looking for.

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited July 2023 Answer ✓

    Hi Larry,

    It will work when the conditions are met, when they are not it will be completely null.

    The solution here is to account for when the holds do not = 'Y' by adding ELSE to your case statement:

    CONCAT(
    CASE WHEN New Logo Hold = 'Y' THEN 'New Logo Hold' ELSE '' END,
    CASE WHEN My Product Customization Hold = 'Y' THEN ', My Product Customization Hold' ELSE '' END
    )

    This is working for me and I believe giving the results you are looking for.

    If I solved your problem, please select "yes" above

  • marcel_luthi
    marcel_luthi Coach
    edited July 2023

    As @colemenwilson said, most Beast Mode functions will return NULL if any of the parts being evaluated return null, this is why having the ELSE statement to return an empty string instead works. If you're not using case statements but something else as part of the values to concatenate, you should enclose those in IFNULL() statements. Just remember that after you've concatenated all the possible flags as strings, you might end up having the end being ', ' in which case just for ease of view, you might want to remove this where applicable.

  • larryjacobs
    larryjacobs Member

    Thank you both. My beast mode now works the way I am wanting it to!

Categories