Hex bins in Alteryx: Part 2

by Daniel Watt

In part one of this blog I showed how to create the coordinates of the hex bins. The next thing we need to do is join these coordinates to the table with locations and counts.  For this example I will use countries and 2016 Olympic gold medals.

Country Gold
UNITED STATES 46
GREAT BRITAIN 27
CHINA 26
RUSSIAN FEDERATION 19
GERMANY 17

We need to generate rows for each country equal to the number of gold medals which then start again for each country.  We can do this in the Generate rows tool.  Configure the tool as below to create a new field called Medal Rows.

generate-medal-rows

The next step is to join the results to the hexbin table we created in the previous blog. Join by specific fields, Medal Rows and Record ID.  To plot these on a map we also need to join the longitude and latitude for each country.  You can find these here.  This is what the table will look like (first 5 rows).

Country Medal rows x y latitude longitude
UNITED STATES 1 -2 0 37.09024 -95.712891
UNITED STATES 2 -1 2 37.09024 -95.712891
UNITED STATES 3 -3 -2 37.09024 -95.712891
UNITED STATES 4 -1 -2 37.09024 -95.712891
UNITED STATES 5 1 -2 37.09024 -95.712891

We’re now ready to connect to the data in Tableau Desktop.  Connect to the data and create a parameter called ‘Scale factor’.  Set this as a float.  Set the initial value to 2.0.   We then need calculated fields to determine where the hexagons appear.

Hexagon x: [latitude]-[x]/[Scale factor]

Hexagon y: [longitude]-[y]/[Scale factor]

Right click Hexagon x in the measure pane and select Geographic role – Latitude.  Right click Hexagon y in the measure pane and select Geographic role – Longitude.  Drop these into the rows and columns on a sheet and the map will appear.  Then drop Country and Medal Rows onto detail on the marks card and you will see the marks appear on the map. Click shape, more shapes to change the shapes to hexagons.  You can download some hexagon shapes from Tim Ngwena’s blog.

Essentially we are creating a hexagonal spiral around the coordinates of the country. The zoom factor determines how large the spiral appears.  The reason I called it scale factor is because the appropriate factor depends on how far you zoom into the map.  Fix the map how you want it to look (how far zoomed in) then adjust the zoom factor parameter until it looks right.

 

Avatar

Daniel Watt

Fri 06 Jan 2017

Thu 05 Jan 2017

Wed 04 Jan 2017