-
1. Re: Map view. Domestic and International.
Allan Walker Jan 16, 2013 7:34 AM (in response to Adnan Akhtar)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.
Adnan Akhtar Jan 16, 2013 7:52 PM (in response to Allan Walker)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.
Allan Walker Jan 17, 2013 7:10 AM (in response to Adnan Akhtar)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.
Adnan Akhtar Jan 17, 2013 5:48 PM (in response to Allan Walker)I responded to the e-mail. Did you get the package?
Regards
-
5. Re: Map view. Domestic and International.
Allan Walker Jan 18, 2013 7:09 AM (in response to Adnan Akhtar)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.
Adnan Akhtar Jan 18, 2013 7:26 AM (in response to Allan Walker)I dont have the excel file handly as it is on my home computer but I have the twbx file- attached.
-
Future travel.zip 547.2 KB
-
-
7. Re: Map view. Domestic and International.
Allan Walker Jan 18, 2013 7:39 AM (in response to Adnan Akhtar)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...
-
Future travel amended.zip 592.9 KB
-
-
8. Re: Map view. Domestic and International.
Adnan Akhtar Jan 18, 2013 7:51 AM (in response to Allan Walker)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.
Allan Walker Jan 18, 2013 8:09 AM (in response to Adnan Akhtar)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.
Adnan Akhtar Jan 18, 2013 8:06 AM (in response to Allan Walker)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.
Allan Walker Jan 18, 2013 8:10 AM (in response to Adnan Akhtar)OK I think I know what you are doing now! Sorry for the confusion.
-
12. Re: Map view. Domestic and International.
Adnan Akhtar Jan 18, 2013 7:54 PM (in response to Allan Walker)Allan,
Attached are the spreadsheets. I removed a few columns before uploading.
-
TripStatus.xlsx 168.9 KB
-
Traveler_ status.xlsx 35.0 KB
-
-
13. Re: Map view. Domestic and International.
Allan Walker Jan 22, 2013 8:42 AM (in response to Adnan Akhtar)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.
-
Amended Traveler_ status.xlsx 37.8 KB
-
Amended TripStatus.xlsx 176.1 KB
-
-
14. Re: Map view. Domestic and International.
Adnan Akhtar Jan 22, 2013 6:54 PM (in response to Allan Walker)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