2 Replies Latest reply on Oct 5, 2018 3:46 AM by Donna Coles

    Split & Pivot with changeable data

    Donna Coles

      I have some data containing licence information.  The system that we source the data from provides 1 row per user, with the licence data stored in a single field, with each licence separated by '+'.  I want to restructure the data to be 1 row per user per licence.

      I have applied a custom split, and then a pivot, which all works fine.

      I've noticed that the pivot step requires me to explicitly add the fields I want to pivot on.  The data I currently have, has up to 7 licences per user, so I have 7 fields to add to the pivot.  However, it may be that the next time I pull the data from the system, a user could have 8 or more licences. If I run this through the flow I've created, I miss some information unless I make a change to the flow.

       

      So a few questions

      1) is it possible to define the pivot step in such a way that it 'dynamically' pivots on all fields named 'licence - split xxx' or similar?

      2) if not, I need to add a check to provide an 'alert' in some way that there is more data and so prompts me to change the flow.  I've figured out a way I could test for this, but not sure how to prosent this as a 'if this happens then STOP else CONTINUE'

       

      I'm new to the product so not entirely sure of all its capabilities just yet.

       

      Thanks

      Donna

       

      PS - I have seen comments that split can only handle up to 10 fields.  Would appreciate confirmation of this too.  Am using 2018.2.3

        • 1. Re: Split & Pivot with changeable data
          Joshua Milligan

          Hi Donna!

           

          There is an idea here (https://community.tableau.com/ideas/9280) that would address this particular challenge.  Please consider voting for it.

           

          In the meantime, you could solve this by adding a union in the flow, after the split but prior to the pivot, with a dummy data set of 1 record that contained the maximum number of fields you anticipate having to pivot.  That would give you explicit fields to perform the pivot and then you could filter out any records resulting from the pivot that were dummy data, plus any NULL values from the actual data that were fields in excess of what actually resulted from the split.

           

          And yes, the split does only allow 10 resulting fields .  You can work around it, but it's not as easy as it should be.  So, here's another idea to vote for! https://community.tableau.com/ideas/9033

           

          Hope that helps!

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Split & Pivot with changeable data
            Donna Coles

            Thanks Joshua!

             

            Please to see I hadn't missed anything.  Ideas have been voted on :-)

             

            For now, given the 10 column limitation, I've manually created the additional 3 calc fields to give me 10 split instances of the field.  I then have explicitly added all 10 fields to the pivot and then removed all the rows with an empty value.

             

            Thanks!

            Donna