4 Replies Latest reply on Jun 30, 2016 10:59 AM by Andrew Davis

    Splitting Delimiter Separated Values

    Andrew Davis

      I have a data set that will contain a variable number of fields within one column, is there a way to have tableau split these into columns?

       

      Example Column - delimiter is always colon between group and count, then semicolon between the sets. 

      test1:40;test2:100:test3:15

      test2:1

      Null

      test3:40

      test1:17;test3:40

       

      I need each of the distinct values of test1, test2, test3 broken out into a column.

        • 1. Re: Splitting Delimiter Separated Values
          Hope Stiles

          Andrew,

           

          When you connect to the data, have you tried this?

          Right click on the column header and choose split or custom split....

          temp split.JPG

          • 2. Re: Splitting Delimiter Separated Values
            Andrew Davis

            Hi Hope, Yes I've tried that.  Neither the split nor the custom split are providing the desired results.  The odd thing is I could have sworn that it worked with an early prototype that I had but something in the data set is now not working well.  I thought it was due to having a space before the semi-colon but I tried creating it that way and it did not fix the problem.

            • 3. Re: Splitting Delimiter Separated Values
              Mark Fraser

              HI Andrew

               

              As far as I know (I maybe proved wrong) but you can only use 1 delimiter at a time, your problem is you have 2 different delimiters.

              It would be the same in Excel (unless you use VBA/macro).

               

              The method I would suggest is a pre-import cleaning step, where you find and replace (ctrl + H in Excel) ; (semi colon) with : (colon) to align the delimiters

              Then do the import, this is then what it would look like (using custom split, all on colons)

              Hope that helps!

               

              Cheers

              Mark

              • 4. Re: Splitting Delimiter Separated Values
                Andrew Davis

                Hi Mark, unfortunately simply splitting the delimiter will not help in all instances.  That will at times work correctly but if you view the data sample that will cause test1 results to go into column one when it is the first result but when test 2 is the first result for a given period it will end up in the column for test1.

                 

                Appears there may not be a good way to account for this type of data in tableau, I'm looking at alternatives.

                 

                Thanks,

                Andrew