6 Replies Latest reply on Mar 29, 2020 1:27 PM by Soumitra Godbole

    Splitting and organizing data

    Kevin Hayes

      Newb here,

       

      I have survey data that has multiple answers (in the form of days of the week). I know how to split the data, but how do I organize the data so I can make a histograph?

       

      The image below shows the data split out.

       

      Screen Shot 2020-03-26 at 10.11.27 AM.png

        • 1. Re: Splitting and organizing data
          Budi Lubis

          Hi Kevin -

          • What other data do you have?
          • You can pivot those new columns into two columns to make it easier to analyze.
          1 of 1 people found this helpful
          • 2. Re: Splitting and organizing data
            Soumitra Godbole

            Hi Kevin,

             

               All you do is rename the split headers starting with Day 1, Day 2 ...... Day 6 (upto whatever is the

            maximum number) then you have 2 choices

             

            1) Do a data Pivot in Tableau

             

                This is a lot easier as you will have 2 additional fields

              [Pivot Field Names]  ie  "Day 1", "Day 2" .... (Header Names)

              [Pivot Field Values]  ie  "Monday", "Tuesday" .... (values for each header)

             

            2) Do a data Join with a single column

                as an excel spreadsheet as shown below

               Day

               Day 1

               Day 2

               Day 3

               . . .

               Day 6

             

               Now you will need to  create a calculated field called [Day Value] using the following logic

             

              Case [Pivot Field Name]

              When "Day 1" Then [Day 1]

              When "Day 2" Then [Day 2]

               . . . .

              When "Day 6" Then [Day 6]

             

              Now your work becomes a piece of cake. Let me know if this was helpful and hope it answers

              your question. Best Wishes !


              Sincerely,
              Soumitra

            • 3. Re: Splitting and organizing data
              Soumitra Godbole

              Hi Kevin,

               

                 In addition to the earlier explanation, here is a sample Histograph using your data and adding an

              additional column for Row ID. For your reference, I have attached the twbx file (ver 2019.1) along with

              the excel spreadsheet. Hope this is helpful and answers your question.

               

              The only question I have is "Where is Thursday ?"

               

              visual.png

               

              I hope there is no "Day 0" as it may get interpreted as "Day O" (Banana Boat Song)

              "Daylight come and me wan' go home"

               

              Best Wishes !


              Sincerely,
              Soumitra

              • 4. Re: Splitting and organizing data
                Kevin Hayes

                Thanks for this Soumitra!

                 

                When I select the split fields, I don't actually get the option to pivot those columns. If I select unsplit columns however, I do have the choice to pivot them.

                 

                What do you think?

                 

                Screen Shot 2020-03-27 at 1.13.09 PM.png

                • 5. Re: Splitting and organizing data
                  Soumitra Godbole

                  You're welcome Kevin ! Sorry I do not know what your data structure is like but tried to figure it

                  out by merely looking at your image of the columns. I may have overlooked something that I just

                  realized.

                   

                  By any chance, other than the leftmost formula 9. Days of Weeks t... , are the other fields that you

                  have labelled as Day1, Day2 ..... Day 6  calculated using a Text function or by doing a split within

                  Tableau ?

                   

                  In case you have done the split in Tableau, you are correct and may not be allowed to do a Pivot

                  but if you can do the split in say MS Excel recent versions (Text to Columns) as shown below, you

                  will be able to do the Pivot.

                   

                  Untitled.png

                   

                  Oh yes, I just realized that even without the Pivot, you will be able to solve this problem using the

                  2nd method that I suggested in an earlier post (not the previous one) wherein you create a single

                  column in excel called Day No with 6 rows since you have 6 split fields - Day1, Day2 ... Day6

                  (No DayO ) depending on how many Day Columns you have above.

                   

                  In case you have more than 10 Splits to do, I would advice you to either use Tableau Data Prep or

                  even better, do it in excel (I just solved a problem yesterday with 21 split fields).

                   

                  Anyway here is the earlier post that I am speaking of (thanks to copy and paste)

                   

                   

                  2) Do a data Join (Cartesian Product join i.e. 1 = 1) with a single column containing 6 rows

                      as an excel spreadsheet as shown below

                     Day

                     Day 1

                     Day 2

                     Day 3

                     . . .

                     Day 6

                   

                     Once you have done the Pivot, you need to create a calculated field called [Day Value] using the

                     following logic

                   

                    Case [Pivot Field Name]

                    When "Day 1" Then [Day 1]

                    When "Day 2" Then [Day 2]

                     . . . .

                    When "Day 6" Then [Day 6]

                   

                  Also make sure you do have one column like an index of Row Value 1,2,3 ... as I had shown in my

                  excel spreadsheet as the Countd of this Row  will give us the Frequency for the Histograph.

                   

                  Lastly I hope this long and boring explanation did not confuse you or take you back to stone-age .

                  If only you had real data, this would have been solved long ago. Best wishes !


                  Sincerely,
                  Soumitra

                  1 of 1 people found this helpful
                  • 6. Re: Splitting and organizing data
                    Soumitra Godbole

                    Hi Kevin Hayes,

                     

                        Hope you're having a good weekend. I was just doing a routine followup to check if

                    you tried the latest solution I posted (that offers an alternative to Pivoting). Do let

                    me know if you have any difficulties. Also it would help to include either a packaged

                    workbook (twbx file) or an excel spreadsheet with dummy data. Take care !


                    Sincerely,
                    Soumitra