How to Count the same record again with Multi-Selection Attributes

PJG · 2024-07-17T16:51:06+00:00

There was an error rendering this rich post.

I have a multi-selection attribute with 19 selection options within it.

I am trying to group these 19 selection options into 5 slices on a pie chart with this code:

case

when Impacted Business Division_p LIKE '%Biologics%' then 'Biologics'

when Impacted Business Division_p LIKE '%CGT%' then 'CGT'

when Impacted Business Division_p LIKE 'China' then 'China'

when Impacted Business Division_p LIKE '%CHI - %' then 'CHI'

when Impacted Business Division_p LIKE '%Small Molecules%' then 'Small Molecules'

else 'Not Defined'

end

The result for Biologics is correct. Then it seems that it won't count CGT if it was previously counted for Biologics. However, if the selection options are Biologics - DPS|CGT & Bioscience, I would want it counted for both Biologics and CGT.

Then it won't count China if it was already counted for Biologics or CGT, and so on.

How can make it count every instance, even if it's been counted for one of the other categories already? Thanks!

Find more posts tagged with

Accepted answers

ColemenWilson

Single-selection attributes are my preference for sure! But when I've had to deal with multi-select I use the split column tile and pivot/unpivot to get one row per selection and then use the data to build visualizations. Did you end up getting what you needed here?

All comments

ColemenWilson

You'll need to split the values out in an ETL so you can count them for each occurrence of the key word. You can do this using the split columns tile in magic ETL. Then you can unpivot your new split columns into rows using the unpivot tile. Here is an example of what this would look like:

Before:

Impacted Business Division	Date	Amount
Biologics - DPS\|CGT & Bioscience	07/16/2024	400

After:

Impacted Business Division	Date	Amount
Biologics	07/16/2024	400
DPS\|CGT	07/16/2024	400
Bioscience	07/16/2024	400

Let me know if you get stuck!

PJG

Hi Colemen, I've never used ETL yet - can you point me to a good guide/video that would be what I need to achieve this? Thanks

ColemenWilson

This is a great place to start: https://domo-support.domo.com/s/article/360055259234?language=en_US

If you provide some sample data I could help out. You can modify sensitive data as needed. The part I'll need is how you split up (what the delimiter is) the Impacted Business Division field.

PJG

Hi Coleman, thank you for the assistance. I'll take a look at the link. Here is some example data with the pipe delimiter.

Impacted Business Division_p
Biologics - example1\|Biologics - example2\|Biologics - example3\|Biologics - example4\|Biologics - example5\|Biologics - example6\|Biologics - example7\|Biologics - example8
Biologics - example5
CGT & Bioscience - example1
CGT & Bioscience - example1\|CGT & Bioscience - example2\|CGT & Bioscience - example3
CGT & Bioscience - CGT
CHI - example1\|CHI - example4\|CHI - example3\|CHI - example2
China
China\|Small Molecules - example2
Small Molecules - example3\|Small Molecules - example1\|Biologics - example1\|Biologics - example2\|Biologics - example3\|Biologics - example4\|Biologics - example5\|Biologics - example6\|CGT & Bioscience - example1\|CGT & Bioscience - example2\|CGT & Bioscience - example3\|China\|Biologics - example7\|Biologics - example8\|CHI - example1\|CHI - example4\|CHI - example3\|CHI - example2\|Small Molecules - example2
Small Molecules - example3\|Small Molecules - example1\|Small Molecules - example2
Small Molecules - example1\|China

PJG

Within ETL, I have a banner stating "The DataFlow feature for Magic ETL is not activated. Please contact support to enable this". I assume this is referring to the company providing us our Domo instance and who are in charge of our datasets?

PJG

In magic ETL, I have the following flow:

Input Dataset > Split Column > Group By > Unpivot > Output Dataset

Where I'm struggling is Group By, and I'm not sure I have these configured correctly:

Select what columns identify the grouping = Is this my new columns 1 through 19 from the split?
Name a new aggregated column = "Total"
Select a column to aggregate that will fill the new column = not sure; don't I want to count across all 19 columns?
Select how to aggregate the column = count

PJG

I have this working after adding a few extra steps, and please feel free to let me know if there was a better way to do this

I added the Group By action where I added my code from my OP to group the 19 values into just 5.

Then in the Add Formula action, I added the following:

CONCAT(`ProjectName`,`Div Grouping`)

This allowed me to use the Remove Duplicates action based on this this new column.

Ultimately though, this highlights to me how frustrating it is to work with multi-selection attributes when coming back to Analyzer/Cards. For instance, if you have a project that impacts all 5 of my grouped values, the cost appears in each bar/slice/etc of the card. I'm sure that could be confusing for end users, and I'm not sure if there's a good solution/best practice here….. other than sticking to single-selection attributes :)

ColemenWilson