5 Replies Latest reply on Feb 25, 2016 12:03 PM by Andy D

    Create a filter based on a column of concatenated strings...

    Andy D

      I have a column of strings that typically comprises of 1-2 strings concatenated with a semicolon but there are 30 or so different categories and it can be + / - the typical count.

       

      For example...

       

      RowColors
      Row 1red; yellow; blue
      Row 2red
      Row 3blue; green

       

      I can filter this pretty easily when I'm on a worksheet but I'm not sure how to set this up for a dashboard so that ...

       

      1) The user knows what categories are available to select from

       

      2) The selection filters based on what is within the column.  Meaning if the user selects the category "red" row 1 and 2 are displayed.

       

      I tried a wildcard match but then #1 is an issue unless I have another tab defining the possible categories.  I would prefer to not add a tab to list out the categories, though.

       

      I was thinking of a calculated field but I'm drawing blanks on how to set it up.  Anyone dealt with this before?

        • 1. Re: Create a filter based on a column of concatenated strings...
          Joe Oppelt

          I think I'm going to need some more details -- and even better would be a sample workbook that we can work together so that you can say, "When (this) happens, I want (that) to result..."

           

          Right now my immediate response from what you've described is to use the calculated field as the filter field.  Just let the user select what he wants from the mix of concatenated values.  But I suspect that's not what you're really shooting for.

           

          If  you want to let the user select one color ("red", for example) you can use the CONTAINS function to grab all rows where the concatenated field contains "red".

          • 2. Re: Create a filter based on a column of concatenated strings...
            Steve Mayer

            In your use case, I would likely recommend pivoting the data if it is possible for you to do so. This means you are modifying your data source, so that instead of:

             

            Row Columns, ColorWithSemiColons

            Row1 Red;Green;Blue

            Row2 Red;Green

             

            You would have:

             

            Row Columns, Color

            Row1 Red

            Row1 Green

            Row1 Blue

            Row2 Red

            Row2 Green

             

            While this means that Row data ends up being duplicated, it makes doing the type of analysis you are describing much, much easier. You would be able to just add a Color filter to view only Red Rows, for example. The main consideration ends up being how to aggregate measures - if you have a measure that you simple want to SUM up, you have to make sure you are only counting the measure once for each Row. But Tableau has ways to handle this.

             

            So how do you pivot your data? There are ETL tools available to do it, but in a pinch, you could also use Tableau using the Split command and re-exporting the data into Excel - opportunities to learn more about Tableau there.

             

            Hope you find this guidance helpful - a lot of learning opportunity on this topic.

             

            -Steve

            • 3. Re: Create a filter based on a column of concatenated strings...
              Andy D

              Hi Joe.  I'll see what I can do with Contains

               

              What I'm hoping for is a filter where I can select from this list...

               

              red

              blue

              green

              yellow

               

              which does a contains search within in the column for the field selected.  So if I select red from that list, I get back 1 and 2.  I'll see if I can set up a calculated field using conditionals and Contains

               

              I'll see if I can add a file with the above in a little bit

              • 4. Re: Create a filter based on a column of concatenated strings...
                Andy D

                Hi Steve.  The data is actually pivoted already in its original form.  I've purposely created a concatenated column of the data because rows are already being duplicated for other requirements.

                • 5. Re: Create a filter based on a column of concatenated strings...
                  Andy D

                  Ended up using a combination of a parameter and a calculated field...

                   

                  If contains([color], [color filter]) then "true" else "false"

                   

                  and color filter is the list of colors to select