3 Replies Latest reply on Aug 14, 2018 12:40 PM by Don Wise

    Cleaning Name fields

    Jo Sterritt

      I'm very new to prep and have a very basic skill level with calculations.  So be gentle with me.  :-)

       

      I have a list of travel data where the Traveller name appears in a variety of formats and I need to combine so that I can report on individual traveller spend.  eg:

       

      SMITH JONES/JOHN MR

      SMITHJONES/JOHN MR1

      SMITHJONES/JOHNMR

      SMITH JONES/JOHNMR2

       

      I've had asssitance with removing the titles (Mr,Mrs etc), but the double surnames are causing me grief.

       

      How do I remove the space between the double surnames before the /  ?  Basically I want the names to appear - SMITHJONES/JOHN

       

      Thank you

        • 1. Re: Cleaning Name fields
          Don Wise

          Hello Jo,

          You can try: REPLACE([Your Field Name])," ","")  So that you get this:

           

          Screen Shot 2018-08-13 at 5.21.17 PM.png

          Screen Shot 2018-08-13 at 5.21.23 PM.png

          Let me know if you have any additional questions; glad to help!  Welcome to Tableau Prep!!  Don

          • 2. Re: Cleaning Name fields
            Jo Sterritt

            Thanks Don

             

            That worked perfectly... but then the other logic I already had created problems.  :-(

             

            The data is pretty odd, so trying to accommodate all its oddness is a challenge.  A sample, columm1 is how is looks and column 2 is what I'm trying to achieve.

             

            Its quite a challenge so any thoughts greatly appreciated.  :-)

             

              

            ZAUNER/BRENDANMR1ZAUNER/BRENDAN
            ZAUNER/BRENDANMRZAUNER/BRENDAN
            SOMERVILLE/ANDREW MR1SOMERVILLE/ANDREW
            SOMERVILLE/ANDREW MRSOMERVILLE/ANDREW
            GRUBB/CAMILLE NICOLA OWTON MSGRUBB/CAMILLENICOLAOWTON
            ROBERTSON/BILLIE MSROBERTSON/BILLIE
            ROBERTSON/BILLIE MISS1ROBERTSON/BILLIE
            ROBERTSON/BILLIE MISSROBERTSON/BILLIE
            ROBERTSON/BILLIE MS1ROBERTSON/BILLIE
            /THUNDER/SYDNEYMSTHUNDER/SYDNEYMS
            /TIGERSHBASYDTIGERSHBASYD
            2018 GOGOL BORDELLO TOUR2018GOGOLBORDELLOTOUR
            5 SECONDS OF SUMMER TOUR 20185SECONDSOFSUMMERTOUR2018
            A B DE CARVALHO ALMEIDA/JOSE FREDERICO MRABDECARVALHOALMEIDA/JOSEFREDERICO
            ABBONIZIO/EMILIO MRABBONIZIO/EMILIO
            ABBONIZIO/EMILIO MR3ABBONIZIO/EMILIO
            ABBONIZIO/EMILIO MR4ABBONIZIO/EMILIO
            AGNEW/MELISSA DRAGNEW/MELISSA
            AGNEW/MELISSA DR10AGNEW/MELISSA
            ALBRECHTSEN/JANET MSALBRECHTSEN/JANET
            ALBRECHTSEN/JANET1ALBRECHTSEN/JANET
            ALBRECHTSEN/JANET2ALBRECHTSEN/JANET
            BORECKI CARMONA/CINDY MISSBORECKICARMONA/CINDY
            BORECKICARMONA/CINDYMISSBORECKICARMONA/CINDY
            LOUEY GUNG/ROZALIND JESSIE MSLOUEYGUNG/ROZALINDJESSIE
            LOUEYGUNG/ROZALIND JESSIE MSLOUEYGUNG/ROZALINDJESSIE
            O'CONNOR/LUCAS PAUL REGINALD MOCONNOR/LUCASPAULREGINALD
            OCONNOR/LUCAS PAUL REGINALD MROCONNOR/LUCASPAULREGINALD
            VAN RAALTE/STEPHEN MRVANRAALTE/STEPHEN
            VAN RAALTE/STEPHEN MR1VANRAALTE/STEPHEN
            VAN RAAY/REBECCA MSVANRAAY/REBECCA
            VAN RAAY/REBECCA MS1VANRAAY/REBECCA
            • 3. Re: Cleaning Name fields
              Don Wise

              Hello Jo,

              I deleted my last as it was in error.  T'was me .  The order of what happens in massaging data is critical and even I didn't catch how I'd written that calc and how it would play out.  So, figured it out mostly for you, leaving only a couple which are a challenge.  The .tflx is attached to this post for you so you can see the steps.  Your output will be the first column...Remove Titles.

               

              I rearranged the order of this calculation so that anything with a 'ABCn' was addressed FIRST, then anything with just 'ABC' would be last and that resolved the first issue. 

              Screen Shot 2018-08-14 at 11.46.05 AM.png

              The other steps removes the spaces but keeps the numbers.  So there's something I think you'll need to address as to whether you'll keep numbers as some of the customer names are tour years/dates and simply removing the numbers would also remove those elements. 

              Screen Shot 2018-08-14 at 12.27.30 PM.png

              On some, because Find or Contains and Replace would globally change all '/' and because you're using that to separate last name/first names, you'll need to simply step in and edit those values where someone entered a preceding '/'.  Unless someone else has an idea on how to extract and replace using a nested REGEX calc.  I haven't seen one though.

              Screen Shot 2018-08-14 at 12.36.44 PM.png

               

              Hope that helps with your efforts!  Don