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

# Create calculated field with country codes

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

Kind regards,

Yulia

• ###### 1. Re: Create calculated field with country codes

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

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

--Shawn

• ###### 3. Re: Re: Create calculated field with country codes

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

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"
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

Thank you very much Jonathan!

It worked perfectly now!