4 Replies Latest reply on Jun 24, 2016 6:51 AM by john a

    Splitting up of single column data into multiple column.

    Amarendu Samal

      Hi,

       

      I have a set of data as

       

      User                     Name                        Occupation                     Location

      1                         Sachin_Tendulkar       Cricketer                         Mumbai,India

      2                          Micheal_Phelps         Swimmer                         Maryland,USA

       

       

      Here as you can see Name column contains two data separated by "_" and Location by ",".

       

      I need to separate these columns as

      1 Name - First Name

                      Last Name

       

      2. Location- City

                          Country.

       

      Is there any way in tableau so that i can split columns as per requirement without changing any thing in my database.

       

      Thanks

      Amarendu 

        • 1. Re: Splitting up of single column data into multiple column.
          Robin Kennedy

          Amarendu,

           

          You will be able to do this by creating some calculated fields in Tableau. Right click in the data window on the left and select Create Calculated Field.

           

          In the Calculated Field window you need to write a formula that will get the first name, this can be accomplished by looking for the underscore and truncating the [Name] string at that point using a combination of the LEFT and FIND functions. A formula such as the following should work

           

          LEFT([Name],FIND([Name],"_")-1)

           

          For last name, a combination of MID and FIND should do the trick

           

          MID([Name],FIND([Name,"_")+1)

           

          For the Location, the formulas will be very similar, you just need to substitute _ for ,

           

          Hope that helps,

          Robin.


          2 of 2 people found this helpful
          • 2. Re: Splitting up of single column data into multiple column.
            Amarendu Samal

            Hi Robin,,

             

            Thanks a lot for the quick reply.

             

            But while using the formula i.e

            LEFT([Name],FIND([Name],"_")-1)

             

            It is showing error as FIND() function takes only constant or aggregate value and same for LEFT().

            So when i am using the name of the column i.e Name  it is not getting executed.

             

            Thanks & Regards

            Amarendu

            • 3. Re: Splitting up of single column data into multiple column.
              Shawn Wallwork

              Amarendu Samal Big Data and Advance Analytics Developer CMC ltd you could try doing this:

               

              LEFT(ATTR([Name]),FIND(ATTR([Name]),"_")-1)

               

              MID(ATTR([Name]),FIND(ATTR([Name]),"_")+1)

               

              But I must warn you Amarendu Samal Big Data and Advance Analytics Developer CMC ltd that this may not work. Amarendu Samal Big Data and Advance Analytics Developer CMC ltd is the [Name] field coming from a secondary data source?

               

              --Shawn

               

              PS: Answered this one for you Dimitri.

              • 4. Re: Splitting up of single column data into multiple column.
                john a

                Amarendu,

                 

                I know this is coming many years late.

                 

                You could create a  calculated field with the  split function.

                 

                The  split function returns a sub-string as determined by a delimiter.

                So this formula returns the first name, substituting 1 for 2 returns the last name.

                 

                SPLIT([Name],"_",1)

                Same logic can be applied to the Location, you need to substitute the delimiter

                 

                RGDS

                John