Inspiring Ingenuity

Alteryx, Bicycles and Teaching Kids Programming.


Alteryx: Big Data and Current Events

Or a National Summary of Food Deserts

ba9b4a63ebf91e8f91c66f1ec3e4d397Food Deserts, areas that are a longer than normal distance to grocery stores, have been an ongoing topic in politics and demographics for a while now.  I first heard about the concept when Rahm Emanuel started talking about it in his run for mayor of Chicago.  I have continued to see articles and blog posts about it and every time I think that Alteryx would make it much easier to create a more nuanced analysis.

They say a picture is worth a thousand words, so I wanted to start with a map.  As I explored in Dot Density Maps, mapping a phenomenon that without exaggerating rural areas can be very hard.  Look at some of the other maps online: here, here, here, etc…  They all show the problem seemingly as a rural problem.  To be fair, a lot of blogs are looking at it from a socioeconomic or health point of view and rural areas do play a large part.  In the map on the left (click for a larger version) each dot represents 500 people in a Food Desert.  It becomes clear that the issue is primarily a suburban issue.  There is a ring around almost every city of food desert.  From an environmental point of view, this is a disaster.  It becomes impossible to walk or ride a bike to get food, so that much more gas is burned and that much more time is wasted sitting in traffic.

Continue reading


Alteryx: Dot Density Maps

As well as tips for writing reusable macros…

I have been continuing down my path of writing a general interest post about Food Deserts.  Most maps you find online of Food Deserts, or any other phenomenon that happens primarily in rural areas make the issue look much larger than it really is.  Looking at a map of the 2008 US presidential election, you would never guess that the blue team won.  Rural areas are a larger portion of the map than they are of the people and so it is very easy to create a misleading map.  I wanted to explore mapping methodologies that properly shows the scope of an issue – not exaggerating it by making it look bigger or smaller than it actually is.  You can click on all the maps in this post for a larger version.

Continue reading


Alteryx: Processing Groups of Records in a Macro

I have been working on a blog post that is going to integrate a variety of different aspects of Alteryx together to paint a larger story.  As I have been building towards it, I have run into a variety of challenges that have grown into blog posts in their own right.  It started with Weighted Medians and continued on with Downloading from TIGER.  Today I ran into a very common problem – I needed to process records in groups.  The solution I outline is extensible to any macro; it should be a useful technique for any data artisan. Continue reading


Alteryx: Connecting to Cloud Services


…or consuming the Google Directions API…

There are many functions available as cloud services that can be used within Alteryx.  For instance, the Alteryx geocoder is fairly good, but sometimes people want more, or just want to try other options.  Instead of asking Alteryx to do something about it (and waiting for a future release), many of the APIs can be utilized from directly inside of Alteryx.  A quick search will find a variety of options, many of which could easily be wrapped by an App or Macro. Continue reading


Alteryx: Downloading from TIGER

or how do I get free spatial data for Alteryx…

I keep having this big idea for a blog post, but getting sidetracked by things that I need before I start.  Its always nice to share, and I figure many other Alteryx users would have the same needs as me.  Last weeks post, Weighted Medians, was one example of this.  This week, for the next step in what I have been working on, I needed a US Block polygon file.  Blocks are the lowest level for which Census data is tabulated.  The census helpfully makes the polygons available for download on their ftp site, but the data is in a separate ZIP file per state containing SHP files.  What I really want is a single  YXDB file for the entire layer.  Downloading 50+ files and then unzipping and converting by hand sounds like a lot of work.  I am a programmer and programmer’s by nature are lazy, so I wanted to make this process easier. Continue reading

1 Comment

Alteryx Mapping: UK at Night

I seem to have quite a few readers from the UK on my blog.  After my post last week showing the US at Night I got requests for a UK version.  I don’t have a lot of data for the UK right now, so here is a quick map with the same methodology for UK population.  When I get a copy of the UK census data, I will produce a map set with a bunch of variables like I did for the US.

UK at Night

Click for a much larger version…

Thanks for reading,



Alteryx: Reading XLSX Directly

Sometimes, it seems that Excel is the most popular database format for our customers.  My response has always been that Excel is not a database, but at the end of the day it doesn’t matter – they have data there that they want to use in Alteryx.  In reality I am sure other formats get used more, its just that I hear about Excel so often because there are so many issues.

Our support for Excel files is through a Microsoft driver.  In the case of modern Excel (XLSX files) that support comes from the Access Database Engine.  There are a bunch of issues with this;  if you have a 32bit version of office installed, you can only have the 32bit driver which makes us have to jump through additional hoops if you have a 64 bit Alteryx.  For various reasons, it is a hard one for us to install as part of Alteryx, so it is left to the user.  This leaves these files unreadable on many machines.

Recently I got the following on our internal support email:

… The issue here is that when you bring the data of the spreadsheet into Alteryx, it rounds up the decimal points to fewer than from data that is showing in Excel. Where the spreadsheet shows 5 decimal points, Alteryx only brings in 4 decimal points. The user doesn’t need the entire decimal range, but at least wants to have the 5 decimals of data. …

Continue reading


Alteryx: Ensuring fields are in a data stream

This week’s question:  How do I ensure fields are in a data stream?

This is a common issue when you are reading data that changes from time to time.  If you use a Crosstab Tool or are reading from an XML file, the output fields can change based on the presence (or absence) of specific input data.  This makes it very hard to write formulas, or really do any down stream processing on those fields.  In particular, what we want to do is ensure fields are present, but NOT change them if they are already in the record. Continue reading