1 of 1 people found this helpful
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.
And I was so sure this would work..... Oh, well.
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.
Countries Test.twbx.zip 18.1 KB
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.
Thank you very much Jonathan!
It worked perfectly now!