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

    Assigning Rows/Entries to Multiple "Groups"

    simone.banna

      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!