3 Replies Latest reply on Feb 28, 2018 12:57 AM by simone.banna

# Assigning Rows/Entries to Multiple "Groups"

I'm running into a bit of a dilemma on a work project and need to tap into the experts for help! Note: The data we are using is confidential. So I've included dummy data in lieu of our actual data.

I have a table that has a [Food] item with corresponding [Labels]. All the [Labels] are separated in ONE COLUMN by commas. See below.

Also note, each [Food] generally has about nine labels. But there are THOUSANDS of different labels (in other words, manual groups would be too tedious). I've simplified for this explanation only.

FoodLabelWeight
AppleRed, Green, Round, Sweet, Sour1.2
GrapeRed, Green, Round, Sweet, Sour, Seedless.25
GrapefruitRed, Orange, Yellow, Pink, Round, Sweet3
PineappleYellow, Sweet, Seedless5

So I've created a calculated field called COLOR and have the following code in it.

IF CONTAINS ([Labels], "Red") THEN "Red"

ELSEIF CONTAINS ([Labels], "Green") THEN "Green"

ELSEIF CONTAINS ([Labels], "Pink") THEN "Pink"

ELSEIF CONTAINS ([Labels], "Yellow") THEN "Yellow"

ELSE "Null"

END

The problem: If Apple is "grouped" as Red, then it does not get grouped as Green.

How can I assign MULTIPLE GROUPS to each [Food]? Because once that is done, I would want to look at each group through a variety of lens - such as getting the average weight of all RED fruits. This is an important part of the exercise, too!!

This has stumped me, so any help would be appreciated!

• ###### 1. Re: Assigning Rows/Entries to Multiple "Groups"

Check the answers by Ivan and Simon

Thanks,

Ritesh

• ###### 2. Re: Assigning Rows/Entries to Multiple "Groups"

If You want Some thing Like this:

You need to Create a Cartesion Join using 1 as Join Calculation . First You need to create a separate excel data file for your Labels in One Column. Join Both Original data nd Excel file of Labels on 1:1 in Data view and create a Calculation for Filteration to get What you Need. You can Further manipulated this data as needed.

Thanks

Deepak

• ###### 3. Re: Assigning Rows/Entries to Multiple "Groups"

Thank you all! Deepak's solution ended up working!