3 Replies Latest reply on Sep 30, 2016 5:29 AM by ra.sae.0

    Filtering on a concatenated string

    Matthew Perry

      Hi there,

       

      I have 1 row per person, with a column for a concatenated list of colours....eg.

       

      PersonColour
      FredRed, Blue, Green
      FrankRed
      GeorgeRed, Blue

       

      I would like to have a filter, where I can choose "Red" to show all three people, "Blue" to show Fred and George and "Green" to show only Fred.

       

      But how to create a filter that pulls apart the concatenated values.

       

      TIA Matt

        • 1. Re: Filtering on a concatenated string
          ra.sae.0

          Hi Matthew,


          There are multiple ways to achieve it. Few simple ways are attached image & Workbook. Please see to it.

           

          Hope this helps.

           

           

          Option 1 - Use Wildcard Match Filter

          Option 2 - Use Parameter & Calculated Field to Filter

           

          • 2. Re: Filtering on a concatenated string
            Matthew Perry

            Thank you for your response. Yes, these are methods I have tried and work for a single colour selection. What I mistakenly left out of the original question is that it is necessary to be able to filter by 2 (or more) colours (or any number >=1).....

            • 3. Re: Filtering on a concatenated string
              ra.sae.0

              Another Option 1 :

               

              Having multiple Parameters to filter them

               

              Another Option 2:


              Using Split function to Filter using one Parameter

               

              if

              contains([Concat Colours],trim(split([Filter Parameter],";",1)) )

              then

              "True"

              ELSEIF len(split([Filter Parameter],";",2)) > 0

              AND contains([Concat Colours],trim(split([Filter Parameter],";",2)) )  then

              "True"

              ELSEIF len(split([Filter Parameter],";",3)) > 0

              AND contains([Concat Colours],trim(split([Filter Parameter],";",3)) )  then

              "True"

              ELSEIF len(split([Filter Parameter],";",4)) > 0

              AND contains([Concat Colours],trim(split([Filter Parameter],";",4)) )  then

              "True"

              ELSEIF len(split([Filter Parameter],";",5)) > 0

              AND contains([Concat Colours],trim(split([Filter Parameter],";",5)) )  then

              "True"

               

               

              END

               

               

               

               

              ; helps to make multiple list and above formula can help up to 5 values, if u need more, just copy the last lines and add 6 , 7 to it.