1 Reply Latest reply on Nov 12, 2018 6:32 PM by Paul Wachtler

    Help Mapping - extract country from a string

    Mikey Michaels

      Hello,

      Hope all is well.

      I have been asked to create a simple global map with the attached twbx file.

      At first, I didn't think this was going to be possible, but I found a field that had a country in it (sales org).

      I was trying to extract the last word from the sales org field (tried using REGEXP_EXTRACT; however, this did not work as intended.)

      Once I have the country, I can map the zones.

       

      Any and all help is appreciated....thanks!

        • 1. Re: Help Mapping - extract country from a string
          Paul Wachtler

          Hi Mikey,

           

          I figured it out with a somewhat long string calculation instead of using regex.

           

          if len([Sales Organization L]) - len(REPLACE([Sales Organization L]," ","")) >= 2 then

          mid([Sales Organization L],FIND([Sales Organization L],"- ")+2,LEN([Sales Organization L])- FIND([Sales Organization L],"- "))

          ELSE

          mid([Sales Organization L],FIND([Sales Organization L]," ")+1,LEN([Sales Organization L])- FIND([Sales Organization L]," ")+1)

          end

           

          The first condition is checking whether or not there's more than one space between the first part of the string and the country.  Some have one space before the dash, some have two spaces, and some have no dash, so the calc is slightly different for the one's that have the dash vs the ones that don't.  For the ones with dashes, it's finding the place of those dashes and grabbing everything else after it.  For the ones without dashes, it's looking for the space before the country and taking all text after that.

           

           

          Screen Shot 2018-11-12 at 9.29.49 PM.png

           

           

           

          Once you create your Country calculated field, right click on it, select Geographic Role, and select "Country/Region".

           

          Screen Shot 2018-11-12 at 9.30.19 PM.png

           

           

           

          Here's a picture of the map that you can now create using your country field.

           

          Screen Shot 2018-11-12 at 9.30.27 PM.png

           

           

          Let me know if you have any questions.


          Best,

          Paul