2 Replies Latest reply on May 28, 2013 5:07 AM by Chris Polman

    Multiple columns under one filter

    Chris Polman

      I'm very new to Tableau and trying to figure out something that's probably pretty basic.

       

      I've got an xls file that's something like this:

       

      FruitGreenRedYellow
      LimeY

      AppleYY
      Cherry
      Y
      Banana

      Y

       

      A mark under a color column indicates the fruit is that color, and some have multiple colors. I'd like to set up a quick filter with checkboxes for Green, Red, and Yellow under a single heading, "Color." Checking/unchecking a box will show or hide the matching "fruits."

       

      I tried a few methods like Combine Fields in the data pane, but didn't get very far.

       

      I also thought about modifying the xls file to something that puts multiple values into single cells, like this:

       

      FruitColor

      Lime

      Green
      AppleGreen;Red
      CherryRed
      BananaYellow

       

      But I'm not sure if there's a way to get Tableau to separate combined values instead of reading them as a single "Green;Red" value.

       

      Is there a way I can make either of these methods work?

        • 1. Re: Multiple columns under one filter
          Shawn Wallwork

          Hi Chris welcome to the forums. You're on the right track in your second solution. In this case it is much better to reshape your data. This is such a common need that Tableau has an unsupported Excel plugin that does the work for you: http://kb.tableausoftware.com/articles/knowledgebase/addin-reshaping-data-excel

           

          This will create all the necessary rows you'll need to get a single color on each row of the new color column. But before you use the reshaper I suggest you do a find/replace on all those blanks so you get N in all the blanks:

           

          FruitGreenRedYellow
          CherryNYN
          BananaNNY
          LimeYNN
          AppleYYN

           

          After you run this through the reshaper it'll look like this:

           

          FruitCol2Col3
          CherryGreenN
          CherryRedY
          CherryYellowN
          BananaGreenN
          BananaRedN
          BananaYellowY
          LimeGreenY
          LimeRedN
          LimeYellowN
          AppleGreenY
          AppleRedY
          AppleYellowN

           

          Now you can basically delete Col3

           

          [Edit: Driving to Home Depot for the 4th and final (I hope, I hope) to finally get the swamp cooler going. I realized how dumb this last statement was. You need that Col3 to use as a filter. Because of this NOT filling out the data with Ns is probably your best option. See below.]

           

          I understand your data is probably more complex than this, so feel free to ask more questions if you get hung up at some point.

           

          Hope this helps,

           

          --Shawn

           

          EDIT: The find/replace step may not be necessary, unless it's important to get an equal number of rows for each fruit. Without adding in the Ns here's what the result would look like:

           

          FruitCol2Col3
          CherryRedY
          BananaYellowY
          LimeGreenY
          AppleGreenY
          AppleRedY

           

          Fewer records, but an uneven distribution, which might affect other data calcs down the road.

           

          Message was edited by: Shawn Wallwork

          • 2. Re: Multiple columns under one filter
            Chris Polman

            Thanks, Shawn. I hoped to avoid reshaping the data, but it sounds like that plugin should make it much easier.