## 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.

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.

Daniel Watt

If I walked this path again

Fri 06 Jan 2017

How to succeed in the Data School

Thu 05 Jan 2017

Uphill battles at the Data School