Got a fun one for grouping Columns together. Need help building

Mickey

This one will take some setup.

I have a card by Style and Color, with values of Total AVAIL STK and then broken out by Size columns, and TOTAL WIP and by Size columns.

The issue is when we filter to a style, we don't need to see the sizes it doesn't have. If we're viewing a purse, it only needs to show "1SZ" since it would be the only one populating. The rest is just noise making the card harder to read.

To do this, I wrote the simple Beast Mode below:

CASE WHEN (1SZ <> 0 OR 1SZ WIP <> 0) THEN '1SZ'
WHEN (XS <> 0 OR XS WIP <> 0) THEN 'XS'
WHEN (SML <> 0 OR SML WIP <> 0) THEN 'SML'
WHEN (MED <> 0 OR MED WIP <> 0) THEN 'MED'
WHEN (LRG <> 0 OR LRG WIP <> 0) THEN 'LRG'
WHEN (XLG <> 0 OR XLG WIP <> 0) THEN 'XLG'
WHEN (XXL <> 0 OR XXL WIP <> 0) THEN 'XXL'
WHEN (3XL <> 0 OR 3XL WIP <> 0) THEN '3XL'
END

This created this card, which in theory is exactly what we need:

The problem, as you may already see, is that it isn't including all of the sizes that populate in the first card. It's taking the first clause, in this case "WHEN (XS <> 0 OR XS WIP <> 0) THEN 'XS'" and defaulting every size after that as XS, since the XS column was populating. When XS isn't populating but SML is, it makes every value "SML" and so on…

Could someone help with how I can write a Beast Mode that only looks at one of the size columns and only labels that one, then moving down the line for the rest of the sizes?

All help is appreciated. Thank you!

ggenovese

Hi @Mickey

It looks as if you're trying to create a "size" field in a beast mode because your dataset doesn't have a size column, instead it has a column for every size. Is that correct?

If so, this problem is better solved in an ETL than with a beast mode.

ggenovese

Hi @Mickey

It looks as if you're trying to create a "size" field in a beast mode because your dataset doesn't have a size column, instead it has a column for every size. Is that correct?

If so, this problem is better solved in an ETL than with a beast mode.

DavidChurchman

Yeah, you should dynamic unpivot the data in MagicETL. If that's not an option, you could color code 0s to be gray to de-emphasize them, which is another way to make sparse tables more readable. (Use black with 100% transparency and select the lighter color font).