12 Replies Latest reply on Feb 4, 2016 12:11 AM by Y K

    Split a field

    Y K

      Hi,

       

      The max number of split is 10.

      Is there any way to set more than 10?

        • 1. Re: Split a field
          Eli Blankers

          Hi Y K,

           

          When you say "split", are you referring to splitting a field in the data source window?  I am not aware of a way to increase the split off column count to more than 10.  Is it possible to split the field once, and split one of the splits again?

          • 2. Re: Split a field
            Y K

            Hi Eli,

             

            Thank you for your reply.

             

            I have a field contain over 30 data split by comma.

            I tried custom split – split by comma - first 10

            All new fields contain only 1 data.

            If the last filed contain all data after 9th comma, I would run the split for the 10th field.

            • 3. Re: Split a field
              Bora Beran

              If you create an extract you can split more. 10 limit is imposed if a particular database has the limitation since live connections do this work on the database. On a live connection, you can get more by nesting multiple split calculations but I do not recommend it since you will likely get a query error. But with an extract you won't have such issues, you can split 40 times if you want to.

              • 4. Re: Split a field
                Y K

                Hi Bora,

                 

                I created the extract file, but it is same as before.

                I connected the tde, and tried 'Custom Split'.

                I can not input over ‘10’ columns on GUI.

                I tried Split off = ‘All’ – it also created only 10 columns.

                Am I doing wrong?

                • 5. Re: Split a field
                  Bora Beran

                  You can write it as a calculated field. E.g.

                   

                  Create new calculated field

                   

                  and enter

                   

                  SPLIT([your field name here', '-', 16)

                   

                  will split your field at - and give you the 16th token.

                  • 6. Re: Split a field
                    Y K

                    Thank you, Bora.

                     

                    OK. Create new alculated fileds.
                    The max number of data in the column is not fixed.
                    I need to find the max.

                    • 7. Re: Split a field
                      Bora Beran

                      MAX(LEN('a-b-c-d')-LEN(REPLACE('a-b-c-d','-','')))

                       

                      replace 'a-b-c-d' with your field's name and replace '-' with your delimiter and this should give you the answer.

                      • 8. Re: Split a field
                        Amanjot Klair

                        Hi Bora,

                         

                         

                        won't LEN('a-b-c-d')-LEN(REPLACE('a-b-c-d','-','')) be good enough, as including MAX() will take the max of the column value based the data in the view.

                         

                         

                        Amanjot

                        • 9. Re: Split a field
                          Y K

                          Hi,

                           

                          I created some new calculated filed.

                          I met new problem.

                           

                          My goal is split a column to multi(over 30) fields, and convert them to
                          rows.

                          I can not use ‘Pivot’ for those new calculated fields.

                          I tried with the Extract file. I can not use ‘Pivot’ for those columns.

                           

                          Is there any way I can convert those new calculated filed to rows?

                          • 10. Re: Split a field
                            Bora Beran

                            No. Because he is looking for the max number of delimiters to decide what to put as an argument for split. E.g. if max is 36, he wants to enter SPLIT([field],delimiter, 36)

                            • 11. Re: Split a field
                              Bora Beran

                              Currently you can't pivot calculated fields and SPLIT is a calculated field. What you can do however is to view the data, select all, the paste it back into Tableau as a new data source. Then you will notice that for this new data source, pivot option is enabled.

                              • 12. Re: Split a field
                                Y K

                                OK

                                It is a large file. I try to use export.

                                 

                                Thank you for your help.