Combining Multiple Flags Using Concat() and case stmt

larryjacobs

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?

ColemenWilson

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.

ColemenWilson

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.

marcel_luthi

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

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