5 Replies Latest reply on Jun 11, 2013 6:37 AM by yulia.khazanova

    Create calculated field with country codes

    yulia.khazanova

      Hi Everyone,

       

      I need to create calculated field where all the Mobile Numbers will be translated to the Counties.

       

      E.g., I have the following Mobile Numbers:

       

      Mobile Number

      41123456789

      33123456789

      231123456789

      34123456789

      31123456789

       

      And I want to create a calculated field where

       

      If Mobile Number starts with 41 then Switzerland,

      If Mobile Number starts with 33 then France,

      If Mobile Number starts with 231 then Liberia,

      If Mobile Number starts with 34 then Spain,

      If Mobile Number starts with 31 then Netherlands

       

      Looking forward to your reply!

       

      Kind regards,

      Yulia

        • 1. Re: Create calculated field with country codes
          Shawn Wallwork

          Try:

           

          IF LEN(STR([Order ID]))=2 THEN 
              CASE LEFT(STR([Order ID]),2)
              WHEN '41' THEN 'Switzerland'
              WHEN '33' THEN 'France'
              WHEN '34' THEN 'Spain'
              WHEN '31' THEN 'Netherlands'
              END
          ELSEIF LEN(STR([Order ID]))=3 THEN
              CASE LEFT(STR([Order ID]),3)
              WHEN '231' THEN 'Liberia'
              // etc.
              END
          END
          

           

          Hope this works.

           

          --Shawn

          1 of 1 people found this helpful
          • 2. Re: Create calculated field with country codes
            Shawn Wallwork

            And I was so sure this would work..... Oh, well.

             

            --Shawn

            • 3. Re: Re: Create calculated field with country codes
              yulia.khazanova

              Thank you very much for your reply Shawn!

               

              I tried to do what you said but for some reason all the countries appear to be NULL instead of names.

              Please see the file attached with the test version. I used your formula in the calculated field "Countries."

               

              I will be very happy if you could help me to solve the problem.

              • 4. Re: Re: Re: Create calculated field with country codes
                Jonathan Drummey

                Hi Julia,

                 

                There are a variety of ways to go about this. One technique is to sort your list of country codes by the length of the dialing digits, for example 1207 is US (Maine) and 1250 is Canada (British Columbia) (4 digit codes), then 372 (Estonia), and then 44 for the UK (a 2 digit code). Once you have that, then you can do a series of nested CASE statements:

                 

                CASE LEFT(STR([Mobile Number]),4)
                    WHEN "1207" THEN "USA"
                    WHEN "1250" THEN "Canada"
                    ELSE
                        CASE LEFT(STR([Mobile Number]),3)
                            WHEN "372" THEN "Estonia"
                            WHEN "972" THEN "Israel"
                        ELSE
                            CASE LEFT(STR([Mobile Number]),2)
                                WHEN "39" THEN "Italy"
                                WHEN "48" THEN "Poland"
                                WHEN "49" THEN "Germany"
                                WHEN "31" THEN "Netherlands"
                            END
                        END
                END
                

                 

                This kind of string processing will be relatively slow, if you're working with more than tens of thousands of records I'd suggest using an extract (so the country code gets materialized in the data extact) or pre-processing this outside of Tableau.


                Jonathan

                • 5. Re: Re: Create calculated field with country codes
                  yulia.khazanova

                  Thank you very much Jonathan!

                  It worked perfectly now!