3 Replies Latest reply on Dec 4, 2016 11:34 AM by Yuriy Fal

    Unique Dimension field for Filter

    Irfan Ahamad

      Hi,

      I have data in the below format-

        

      Project NumberOwner
      1202Darren Powers; Phillina Ober
      1950Phillina Ober
      1944Phillina Ober
      1347Phillina Ober
      1877Mick Brown; Phillina Ober
      1895Lycoris Saunders
      1213Jack O'Briant
      1034Maria Etezadi; Darren Powers
      1550Maria Etezadi
      1830Maria Etezadi
      1718Maria Etezadi
      1306Maria Etezadi
      1504Maria Etezadi; Chris Selesnick
      1641Maria Etezadi
      1624Vivek Sundaresam; Mick Brown
      1510Vivek Sundaresam
      1821Melanie Seite
      1924Melanie Seite
      1198Maria Etezadi; Anthony Jacobs; Phillina Ober
      1222Anthony Jacobs
      1055Seth Vernon

       

      I wants a filter which will show unique name of owner like-

           Darren Powers

           Phillina Ober

           Mick Brown

      And if i select Phillina Ober then it will show me all project number where Owner contains this name, I have tried to do the same using Parameter and Contains function but the problem is with Parameter you can't select multiple user name.

       

      Any help appreciated.

       

      Regards,

      Irfan

        • 1. Re: Unique Dimension field for Filter
          Yuriy Fal

          Hi Irfan,

           

          Basically, you have to to a simple data transformation -- I call it "Split-then-Pivot".

          Both Split and Pivot options are available in Tableau -- but not so for a Combo of them.

           

          Split() is a Row-Level calculation, which could be invoked from the interface

          (Transform --> Split on any Dimension Pill of a String Type).

          In your particular case, one could split the Owner dimension

          into three fields using ; (semicolon) as a delimiter.

           

          Pivot is a datasource transformation option in Tableau

          which is available only when using file datasources

          (Text / Excel / Statistical).

           

          Ideally, if one could do Pivot on Row-Level calculated fields

          (such as the ones derived from a Split), then everybody would be happy :-)

          But the Pivot option is available only for the fileds materialised in the datasource.

           

          What can we do now, while we're waiting for the Project Maestro?

          We could make both steps in sequence (manually, of course) --

          Splitting the Owner field, preparing a detailed view (Sheet 7),

          materialising the view into a static Clipboard dataset

          (via Select All Marks and Copy-Paste) and subsequently

          Pivoting (then materialised) Owner - Split N fields after that.

           

          Please find the attached workbook with that steps done.

          Hope it could help.

           

          Yours,

          Yuri

          • 2. Re: Unique Dimension field for Filter
            Irfan Ahamad

            Hi Yuri,

             

            Thanks a lot for your quick reply. Good explanations, I already tried with same way but unfortunately my data source is Microsoft SQL server and this is not working.

             

            Do you have any other option to do this.

             

            Any help appreciated.

             

            Regards,

            Irfan

            • 3. Re: Unique Dimension field for Filter
              Yuriy Fal

              Hi Irfan,

               

              If you're on MS SQL Server 2016,

              you would be using STRING_SPLIT function.

              In Tableau it would be a Custom SQL like this:

              SELECT

              t.project_number as [Project Number],

              f.value as [Owner Split],

              FROM your_schema.your_table as t

              CROSS APPLY STRING_SPLIT( t.owner, ';' ) as f

              Yours,

              Yuri