5 Replies Latest reply on Jun 30, 2015 5:10 AM by Joshua Milligan

    multi-assign attributes with Tableau

    Gabriel Nistor

      Hi all,

       

      I am new with Tableau.

       

      I'm coming from Endeca (Oracle) world and I'm trying to adjust my brain to new ways of dealing with data.

       

      Do I have any simple/native functionality to allow me to deal with multi-assign attributes in Tableau?

       

      Thanks.

      - Gabriel

        • 1. Re: multi-assign attributes with Tableau
          Jason Wieth

          Hello Gabriel,

           

          I'm not familiar with multi-assign attributes, but from the information in your link I think the data prep features in Tableau Desktop would be able to handle it.

           

          The two functions I have in mind are the Split function and the Pivot function. Both are accessible from the data connection screen:

          Split

          Pivot

           

          The splitting would be needed if the attributes are imported as a single delimited field. The pivot will help to drop all of the like values into a single column. From the example data in the link, the resulting table would have two columns: Movie Title, and Actor. This will put the data in a format most easily used for visual analysis with Tableau Desktop.

          • 2. Re: multi-assign attributes with Tableau
            Gabriel Nistor

            Hi Jason,

             

            Thanks for your reply. However, Split & Pivot are not doing exactly what I want.

             

            I would have liked to be able to do some data discovery quickly (quick and dirty), meaning that avoiding having to go through complex data cleansing and transformations, while being able to understand the data would have been a great feature of Tableau. Or maybe this exists, but I'm just unaware of it  ...

             

            Let's assume that I have some raw data like this.

             

            CompanyNameIndustriesEmployees
            Company1Manufacturing, General Business, Telecommunication, Healthcare12342
            Company3High Tech, Healthcare86
            Company2Financial Services, Manufacturing,  Media Retail1000

             

            I would like to be able (with minimum amount of work)  to determine  the workforce potentially available across all listed companies for each industry on which those companies have or had projects.

             

            So, something like this:

             

            Manufacturing13342
            General Business12342
            Telecommunication12342
            Healthcare12428
            High Tech86
            Financial Services1000
            Media Retail1000

             

             

            This is OOTB available when going via Endeca.  So, do I have any means of doing it also using Tableau?

             

            Thank you once again!

            • 3. Re: multi-assign attributes with Tableau
              Joshua Milligan

              Gabriel,

               

              You are correct in saying that the Split & Pivot doesn't give you what you want.  For now, Tableau only supports one or the other, but not split and then pivot.  Hopefully we'll see that in future versions (you can vote up this idea: http://community.tableau.com/ideas/4477 to voice your support for the feature).

               

              In the meantime, I think you are unfortunately stuck with working on the data in the back-end or using some kind of custom SQL to pivot the table.  If you know the upper limit to how many industries can make up the comma separated list, then you could try a work-around like this (admittedly tedious):

               

              • Perform a cross join to an N record table (where N is the upper limit) which has a column giving you values 1 through N.  Your data set will look like:

               

              NCompany NameIndustriesEmployees
              1Company1Manufacturing, General Business, Telecommunication, Healthcare12342
              2Company1Manufacturing, General Business, Telecommunication, Healthcare12342
              3Company1Manufacturing, General Business, Telecommunication, Healthcare12342
              4Company1Manufacturing, General Business, Telecommunication, Healthcare12342
              1Company3High Tech, Healthcare86
              2Company3High Tech, Healthcare86
              3Company3High Tech, Healthcare86
              4Company3High Tech, Healthcare86
              1Company2Financial Services, Manufacturing,  Media Retail1000
              2Company2Financial Services, Manufacturing,  Media Retail1000
              3Company2Financial Services, Manufacturing,  Media Retail1000
              4Company2Financial Services, Manufacturing,  Media Retail1000

               

              • Create a series of calculated fields in Tableau to capture a split for each position (up to N).  Unfortunately, the Split function requires a literal integer, so we can't just use N as an argument:

                        TRIM( SPLIT( [Industries], ",", 1 ) )

               

                        TRIM( SPLIT( [Industries], ",", 2 ) )

               

                        ... and so on.

               

              1.png

               

               

              • Then one final calculated field to select the right split field based on N:
              CASE [N]
              WHEN 1 THEN [Industries - Split 1]
              WHEN 2 THEN [Industries - Split 2]
              WHEN 3 THEN [Industries - Split 3]
              When 4 THEN [Industries - Split 4]
              END
              

               

              • Now, you can create the view you want:

              2.png

              And filter out the blank Industry values to get:

              4.png

               

              If you are creating an extract, I would recommend adding the exclusion of blank Industry as an extract filter.

               

              Admittedly a lot more work than you'd probably like, and hopefully something that will be OOTB in the future, but at least it can be done!

               

              Best Regards,

              Joshua

              • 4. Re: multi-assign attributes with Tableau
                Gabriel Nistor

                Hi Joshua,

                 

                Thanks for your very detailed answer!

                 

                It is indeed not what I was hoping for, but it's painting at least  the idea of what one could do when confronted with such challenge (in Tableau 9).

                 

                I have voted for supporting the push for an OOTB (transparent) implementation, as this approach would not be feasible anymore, if  having to deal with multiple multi-assign attributes at once (again as Oracle EID is currently capable of).

                 

                Best regards,

                Gabriel