Thursday, 9 January 2014

Dual Marks on Maps.

I was recently approached by Dan who wanted some help getting two sets of marks into a single map. 

"Attached is a single Excel file that contains what amounts to three separate data sources, each in its own sheet. We don't have the ability to merge any of these prior to import into Tableau.

I've only included data for Aberdeen for size reasons. But the principle will be the same for the entire UK.

The three files are:
  • File 1. A geocode file. This includes all of the postcodes in the country, with postcode, lat/long of the postcode's centroid, and country
  • File 2. Our "M" file. This includes specific postcodes for the "M" items
  • File 3, Our "G" file. This includes specific postcodes for the "G" items, broken down into Type 1 and Type 2.
We need a single map that contains icons. One type of icon at the centroid of each "M" postcode; one type of icon at the centroid of each Type 1 G postcode; and another type of icon at the centroid of each Type 2 G postcode.

Is this possible? I'm struggling to get two data sources to appear on a single map."

Seems straightforwad enough so lets load the data into Tableau and see what we can do. We need all three sheets so after opening the sheet we select the Multiple Tables option. By default it chooses the first sheet in the Excel workbook as the master sheet, which is fine for our purposes. 

We then need to Add Table and select the 2nd sheet.

Tableau knows the the postcode field is present in the two datasources so selects that as the join for us, which is nice. However for this dataset the master file has many more records and we might want to keep that. The default join is an inner join meaning that only records found in both sets of data will be returned, excluding a lot of the postcodes not present in the second file. So we click on the Join tab and change the join type to left. Now this will bring back all the records of the first sheet regardless of whether there is a join or not. 

We repeat this for the 3rd sheet. Select ok and our data set is now ready for us to look at.

The first thing we see is that Tableau has noticed that we have geographical data and has set the datatype for us, always so helpful. Lets rename our data sources to make it a little easier to see whats going on. 

Lets take a look at the Master data (File 1 -Geo$). it has a county, postcode and two measures, Lat and Long. If we ctrl select the Lat and Long measures and select the map from the showme drop down we get this.

This isnt what we expected to see, a single data point. The reason is that Tableau has aggreated the matures and is showing the average Lat and Long point on the map. Going to the Analysis menu and unticking Aggregate Measures gives us a map that looks like this.

We now have every one of the 16614 marks on the map. The next step is to colour or shape code them so that we can identify which are M, and which are Type 1 or Type 2. Tableau has joined our secondary data sources on the postcode so we can drag the Type dimension onto the colour shelf and see what we get.

However its hard to see whats going on as there are many more null values so it obscures the marks underneath. Dragging the null value to the bottom of the colour shelf puts those marks at the bottom of the map display so we can see the Type1/2 marks better.

Next we need to colour the marks that appear in the M sheet of the Excel file, however this sheet hasnt got a Type dimension which would allow us to combine them together. We can get round this using calculated fields. 

First we need to create a psuedo Type field for the M sheet data. Whenever there is a date in the data we need to return "M" otherwise null. 
Now we can create our dual axis map. First drag another Lat and Long onto the shelf to produce 4 maps

Now we drag our M calculated field to the colour shelf of the second set of maps. We are not interested in the null values so we want to remove them. If we exclude them then this will also remove some of the non-null values from the other set of data. Instead we use the hide option which just removes them from the view 

Now right click on the right hand lat and long pill and select dual axis

Add it to a dashboard, and hide the colour legend titles and finally we get this.

Matt Francis

Author & Editor

Tableau Zen Master, Social Ambassador, Wrangler of Data, Vizzer of Data