-
1. Re: Map view. Domestic and International.
Adnam, try joining the table back upon itself.
Something like
SELECT x1.[CONUS] as CONUS, x2.[OCONUS] as OCONUS
FROM [USAGIS].[dbo].[map] x1
left outer join
[USAGIS].[dbo].[map] x2
on x1.[ID]=x2.[ID]
-
2. Re: Map view. Domestic and International.
Hi Allan,
Thanks for responding. I'm a new user looking to buy Tableau so not really familiar how to use table joins yet.
My field is called State/Country which contains all locations. All the data exist in one spreadsheet. Can you provide steps to achieve that.
Thanks,
-
3. Re: Map view. Domestic and International.
Hi Adnan,
Can you attach the spreadsheet and I will take a look? Also, you say you are looking to buy Tableau. Does this mean you are using Public right now? Or a demo copy of desktop?
Best Regards,
Allan
-
4. Re: Map view. Domestic and International.
I responded to the e-mail. Did you get the package?
Regards
-
5. Re: Map view. Domestic and International.
Hi Adnan,
Can you attach your workbook as a zip file and the excel file as a zip here? As in do a file attachment in the forum post?
Thanks,
Allan
-
6. Re: Map view. Domestic and International.
I dont have the excel file handly as it is on my home computer but I have the twbx file- attached.
-
7. Re: Map view. Domestic and International.
Adnan,
Here's a quick amendment. I (think) this is what you mean...CONUS here means contiguous United States (48 states less Hawaii and Alaska. Is this correct for you?) The other thing was if you have worked with dashboard tabs as well as workbook tabs.
Let me know...
-
8. Re: Map view. Domestic and International.
Unfortunately, this is still displaying the OCONUS locations (or domestic) under your CONUS (international) tab when I opened in Tableau. Same issue I was having before. Basically anything that's 2 digit state code would be domestic and three digits is international. Tableau recognizes that but when I switched the geographic locations to "state" and it would only show Domestic location, and when I switch to "country/region" it only shows on both tabs.
-
9. Re: Map view. Domestic and International.
We can use filters though. For you: CONUS = USA OCONUS = International?
Also, when you are able to post the excel file, we should be able to sort all of this out for you no problem.
Do you know if the fields have Country, State, City etc? We can try geo-coding them once we have the Excel. I have a script that may help. Script is here. Click on the geocode tab.
-
10. Re: Map view. Domestic and International.
Correct. I'll use the term "domestic" and "International," I'll also get you a fresh set of data to work with.
I appreciate all your help.
-
11. Re: Map view. Domestic and International.
OK I think I know what you are doing now! Sorry for the confusion.
-
12. Re: Map view. Domestic and International.
Allan,
Attached are the spreadsheets. I removed a few columns before uploading.
-
13. Re: Map view. Domestic and International.
Hi Adnan,
After looking through the "trip status" worksheet, I can see straight away what your issue is. It has a column I'd split out - the State/Country. I think this needs to be split into two columns, State and Country. You could do this by Calculated Field in Tableau, but really, it would be better to do this in Excel. Then you could easily filter on city - [other] = International. I'd also like to consider setting up "Latitude" and "Longitude" columns, and populating them using the Geo-coder spreadsheet I sent you. How are you handling nulls? There are 22 rows with no location data.
I've attached amended spreadsheets. There are a couple of things you are going to have to watch for. Ambiguous places for one: "Korea" could be North or South, but I'm assuming South. Without a city listed, I think it might be safer to use the capital. Have a go at populating those columns. The Lat/Long columns can come later, but they will help with ambiguity.
Once completed, filtering your data for international/domestic will be easy.
-
14. Re: Map view. Domestic and International.
HI Allan,
I created a similar spreadsheet with same results as yours. I renamed the State/Country to "State" and added another column "Country." I seperated by field length of 2 or 3. Field with 2 digit state codes are under State and 3 digits are under "country." I'm still encountering the same problem. I can drop state or country and it populates the data but when I drop them together, the data dissapears. As far as the 22 rows with blank data, its bad data because they never came with my dowload so I will have to manually fix each line.
Adnan