Learn how to refine your Tableau visualizations by excluding irrelevant data, creating calculated fields for missing geographic information, and assigning proper data types for accurate mapping. This article walks through resolving issues with unrecognized city data to display a complete and accurate international map.
Key Insights
- Filter out unwanted values like "MLS" from the city data by using the Filters card and selecting specific entries to exclude.
- Create a calculated field using an IF statement to assign a country to unidentified cities such as Montreal, Vancouver, and Toronto, enabling Tableau to recognize them as part of Canada.
- After creating the country field, update its data type to a geographic role and add it to the detail mark to ensure the cities appear correctly on the map, a step demonstrated in the Tableau training from Noble Desktop.
This lesson is a preview from our Tableau Course Online (includes software). Enroll in this course for detailed lessons, live instructor support, and project-based training.
So, if you did what I did, that should have corrected the issue. Next issue, excluding values. MLS is not a city.
There's nothing here to fix. It's not a misspelling. It's just a field that has been added to the database that I don't want to include in my results.
So, I'm going to take the city field and bring it into the filters card, because I want to filter out MLS. So, I'm going to take city, drag it into filters. This automatically pops up a list of cities.
I have a couple of options. I could look for MLS, and then click OK. I do have another option.
I could select none, and if I choose exclude, I can select only the one I want to exclude. Now, MLS has a dash. That will be eliminated from the list of values for city.
So, now all I have to do is click OK, and now this reduces my issues to three unknown. Two down, one more issue to go. I have three Canadian cities.
I'm going to zoom out. The Canadian cities would show up above this border line. They're currently not visible.
When I get through with what I need to do, you will see the three Canadian cities show up above the U.S. So, we tell you the solution to this is creating a country calculated field. So, I'm going to create a country calculated field. It's a field that doesn't exist, but I'm going to create it.
Right now, there is no country here. So, on the country level, if a city belongs to a country, it will be recognized. Right now, there is no country, so the U.S., by default, is the only country that's recognized in Tableau by default.
So, I need to put in the country. So, I'm going to create a calculated field, and I'm going to call it country, and then I need to write an if statement. I'm going to say, if the city is in, and when I look at this, this reminds me of SQL.
This is exactly how you would do this in SQL if you were doing a filter. If city is in, and then open parentheses, and then double quotes, you list all the values separated by a comma. So, if, and then when I start to type city, there's my field with the globe, is in, open parentheses, single quote, Montreal, comma, Vancouver, comma, Toronto.
Now, I'm typing this in, but I could also just as well copy it directly from the PDF. Then, Canada. So, if you see those cities, then the country is equal to Canada.
For those of you who just took the SQL course, this is basically like a case statement. Else, U.S., and if, no, just end. Alright, so, I'm going to click OK.
Yay! Wait a minute, where are my geographic locations? I don't ask questions too much. I'll ask questions here just in case someone knows. Does anyone know why I'm not seeing my Canadian locations? There is one step that's left.
So, I'm going to go to the PowerPoint. The one step that's left is the data type for my field. I called it country, but it defaults to text.
How do I know? It says ABC. I haven't told Tableau this is a geographic role. If you put it on one line, it will still work.
It's just put on separate lines just to make it look readable. Like, this is how if statements, if, then, else, end. But, yes, if you put it on one line, it will work.
Let me put it on one line just to prove that it does. I'll go here. One of the ways you know it works is if this says the calculation is valid, that means that the syntax is correct.
Even if I type the words incorrectly, as long as the structure of the syntax is correct, it'll say it's valid. Like, I could say instead of US, it's equal to Garfield. Now, you know that's wrong, but the syntax is correct.
It says the calculation is valid. So, I'm going to go back and put US, of course, and I'm going to click OK. The thing that's missing here for my three cities to show up is I need to go over to country, and I need to change the data type.
So, let me go to change the data type, and I want this to be a geographic role. Here it is. Geographic role, and I want to choose country.
Make sure not to choose county. People have done that in other classes. So, if I choose country, that should do it, although I think there's one more step.
I need to drag the country field to the detail mark. This will add it to my map. So, I'm going to take country.
I'll drag it over to the detail. Yay! There are my Vancouver, Toronto, and Montreal. Toronto has a huge salary circle.
Almost as large as anywhere else. If I look down at the bottom, all my errors have been taken care of. So, I was able to troubleshoot my issues, and I now have an international map.
Alright, so that completes that exercise. I'm going to save this file. I'll choose save as, and I'll call it Day 2 Map.
