3 Replies Latest reply on Feb 7, 2019 9:03 AM by Nick Parsons

    Split First and Last Name

    Mike Chao

      Hello,

       

      For my worksheet I have several sales reps displayed in the following.

       

      JohnSmith

      John Smith

      Jonathan Smith

       

      MikeLee

      Mike Lee

      Michael Lee

       

      Because of the space and Michael and Joanthan, its showing Mike Lee and John Smith as three separate sales rep when they are the same person. Anyway to combine them into one name or put a space between the first and last name?

       

      I've tried a replace calculation but its not working.

       

      Thanks

        • 1. Re: Split First and Last Name
          Ken Flerlage

          If you knew all the values you'd like to conform to a single value, then I'd suggest creating a separate data set that will define how you wish to conform them. For example, say this is your original data set:

           

          Name
          JohnSmith
          John Smith
          Jonathan Smith
          MikeLee
          Mike Lee
          Michael Lee
          John Doe
          Jane Doe
          Mike Chao

           

          Create a new data set like this:

           

          NameConformed Name
          JohnSmithJohn Smith
          John SmithJohn Smith
          Jonathan SmithJohn Smith
          MikeLeeMike Lee
          Mike LeeMike Lee
          Michael LeeMike Lee

           

          Then, in use a left join:

          Finally, use a calculated field to give you either the confirmed name or the original name (if there is no conformed name):

           

          Final Name

          // Use the conformed name if it exists.

          IF ISNULL([Conformed Name]) THEN

              [Name]

          ELSE

              [Conformed Name]

          END

           

          1 of 1 people found this helpful
          • 2. Re: Split First and Last Name
            Joe Oppelt

            I would look to clean up this data outside Tableau.

             

            There are so many permutations of possibilities in data cleanup like this.  For instance, How would you know that John and Jonathan are the same guy?  Even more, take a name like Richard.  You could have Richard, Rick, Ricky, ****, Dickey, and they could all be the same guy.  Or different guys.

             

            And when the data entry did not put in a space between first and last names, finding how to make that break might be difficult.  I guess there could be some calc that looks at a character, and if it is uppercase and the prior character is not, then that would signal a break between first and last name, but that approach would choke on a last name like McCarthy.  (You would need to concoct some sort of iterative loop as well, to look character-by-character, and Tableau calc syntax doesn't have the concept of looping.)

             

            There are better tools than Tableau to do this sort of data cleanup.

            1 of 1 people found this helpful
            • 3. Re: Split First and Last Name
              Nick Parsons

              Agree with clean up ideas. But if you don't clean up, a regex could give you spacing for a quick fix.

               

              First Name =

              REGEXP_EXTRACT([Name],'([A-Z][a-z]*)')

               

              Last Name  =

              REGEXP_EXTRACT([Name],'.+([A-Z][a-z]*)')

               

              or Full Name =

              REGEXP_EXTRACT([Name],'([A-Z][a-z]*)') + " " + REGEXP_EXTRACT([Name],'.+([A-Z][a-z]*)')

              1 of 1 people found this helpful