Tableau Mapping Using Alteryx

Back in July Alteryx released Alteryx for Visual Analytics; a version of Alteryx Desktop specifically tailored
to deliver data natively to Tableau Software.  Since then I have begun to explore what Tableau is all about and in my opinion the two products are a beautiful pairing:  Alteryx allows you to do all of the preparation, heavy lifting, cleaning, blending and reformatting of your source data; and Tableau provides you with beautiful interactive visualizations and dashboards of that data.

One particular area that I looked into early on was the interactive mapping capabilities of Tableau.  You can create some great looking interactive maps in Tableau using a number of built in geocoding levels:

But what if your data isn't at one of those levels?  Well here I have to say a big thanks to +Craig Bloodworth a Tableau Zen Master from The Information Lab; a Tableau and Alteryx partner based here in the UK.  Craig has set up the website http://tableaumapping.bi/ where Tableau users can download spatial datasets for use in Tableau and he was kind enough to educate me in what format these files needed to be in.

Which for those of you interested is this:

Where each row is an ordered point in the outline of the polygon it is going to draw.  Effectively a dot-to-dot for Tableau to follow and render the polygons.

We quickly realized that creating this format using Alteryx would be straightforward using the spatial tools available and put together the below workflow to do just that:

Next step?  Wrap into a macro of course so we can reuse it time and time again by dragging a single tool to the canvas:

If you want the macro to use in your own modules you can download it here.

From there I decided it was time to contribute some mapping files to the site.  Using Wikipedia I quickly found a description of the Administrative Divisions of France and a quick Google search found an open source site where I could download the data (http://professionnels.ign.fr/geofla).  The only problem was that the site only contained four of the geographical levels listed on the Wikipedia page.  I was missing the highest geography level, Regions.  But the problem was quickly fixed when I realized the next level down (departments) included a region field, so I just used a summarize tool in Alteryx to summarize the departments into the Regions and problem solved:

From there it was just a case of selecting which fields I wanted on my output and getting the files off to Craig to publish.

On this coming Monday myself, Craig and +Allan Walker are challenging ourselves to see how many mapping datasets we can add to http://tableaumapping.bi/ in one day.  So if you have any requests for datasets you would like to see then let us know in the comments below or on twitter.  Follow our progress on Twitter during the day with the hashtag #tableaumapping.

Dynamic Formula Macro

DynamicFormula.yxmc

The Dynamic Formula Tool allows you to dynamically create formulas using a data feed. Effectively allowing you to write formulas by using other formulas, the summarize tool or other techniques. Great for when you need to write a long or complex formula or create a lot of formulas and don't want to do it manually.

Download
Help

What Does it Do?

Ever wanted to generate formulas using formulas?  Then the Dynamic Formula macro might be just what you are looking for.

How Does it Work?

First released on this blog back in May 2010 http://www.chaosreignswithin.com/2010/05/dynamic-formula.html; that blog post provides some more background to the tool.  This release sees the module upgraded to horizontal layout and makes use of some of the new Alteryx features that have been added since it was first written:

EscapeXMLMetacharacters()

This new method is ideal for when you are building your own XML as it correctly escapes the XML meta characters.Which are " ' < > &.

So now the tool can dynamically build formulas with greater than or less than comparisons.

Output fields change based on the data input into the macro

This is a new check-box on the macro properties window and the dynamic formula macro is a perfect example of what it is for.  So at configuration time we don't know what fields will come out of the macro because the field names of the new fields are determined by the data that is input into the bottom input.  Without this check-box checked the macro forgets what fields it is outputting whenever the module config is refreshed.  With it checked then after the first run it remembers its output fields, which is exactly what you want.

Dynamic Replace Tool

It is also worth mentioning that since I originally wrote this macro the dynamic replace tool has appeared in the Tool Palette, which with a bit of manipulation I believe can be made to perform the same functionality as this macro.  However I still like the Dynamic Formula for its simplicity.

Another New Alteryx Blog

As if one new Alteryx blog (inspiringingenuity.net) wasn't enough, there is now another Alteryx themed blog by no other than the reigning Alteryx Grand Prix Champion - Chris Love.

Check it out here http://sciolisticramblings.wordpress.com.

His latest blog post creates a bi-variate themed map macro using some great custom XML manipulation.

He also has some great posts on:

• Using Alteryx to compete in Kaggle competitions - http://sciolisticramblings.wordpress.com/2013/10/19/titanic-analysis-little-effort/

• Tableau - http://sciolisticramblings.wordpress.com/2013/10/26/tableau-its-not-excel-what-every-new-user-needs-to-know/; http://sciolisticramblings.wordpress.com/2013/10/27/exploring-food-hygiene-ratings/

• Building Interactive Html Maps - http://sciolisticramblings.wordpress.com/2013/10/26/building-interactive-html-maps-with-r-and-alteryx/

The interactive maps post makes use of some of the experimental work that Ned Harding (Alteryx's CTO) has being doing over on his blog around interactive charting.  If you haven't been following these posts I would highly recommend that you take a look.  He is just wrapping the series up so a great time to read the whole lot at once. http://inspiringingenuity.net/2013/11/11/alteryx-a-reusable-nvd3-macro/

Look out for a macro from me using some of his techniques coming soon...

Macros - Version

Before starting this post on the Version macro I would like to share with you a new page which I have added to the blog which will contain a consolidated list of the macros which I have built and published here. It is easily accessed from the Macros link on the menu bar above.

And the first macro to be released to that page is the Version macro:

Version.yxmc

This macro can be embedded into other macros to allow checking of the version against an XML file stored on the web.

Download
Help

What Does it Do?

I had the idea for this macro several years ago and actually presented it at the developer part of the Inspire conference in 2012.  But it never actually made it to this blog until now.

The idea behind the macro is that it allows other macros to be versioned and to check against a web page to see if there is a newer version available.  Now my initial plan was that this check would be done every time the macro that used it was run.  Unfortunately downloading the web page that contains the versioning information, while not terribly slow (a few seconds), is slow enough that I don't want to do it every time one of my macros is used.  I think this limitation is some of what stopped me posting this macro until now.

So in the released version of the macro I have come up with a compromise.  The macro is embedded in other macros as intended, but by default it does nothing. (All of its tools are disabled by a tool container).

Usage by End User

Here it is being used in the Dynamic Formula macro:

Then when a user wants to check that their macro is up to date they:

1. Open the macro they want to check
2. Click on the version macro
3. Check the Check Box to enable checking (see screenshot right)
4. Run the module.

They then get messages in the log which tells them if the macro is the latest version available:

or a message telling them I have released a new version:

Usage by Macro Developer

If you would like to use this in your own macros then feel free.  To set it up you just drop it into your macro, point it to an XML file you have hosted somewhere on the web and enter the version number to match the version number in your XML file.  The XML file needs to look like this:

You can copy the example here: versions.ChaosReignsWithin.com\Example.xml

Then when you update your macro in the future, you just need to bump up the number in the version macro and in the XML file and include whatever message you would like to tell your users in your XML file.  Then anyone who has an old version of your macro and checks for updates will get your message.

How does it work?

The module is fairly simple

The two text inputs on the left get updated from each of the two questions in the macro and the tool container which contains the whole module gets enabled/disabled by an action which is fed by the check box.

From there:

• A download tool grabs the XML from the web
• We use an XML parse tool to digest the XML
• A join tool brings in the version number from the parent macro
• We then compare the versions and use a series of message tools to display the various messages to the end user.

Alteryx 8.5 - New Features - Tool Palette and New Tool Icons

With the release of Alteryx 8.5 and the new features it brings I have been thinking it is time for a refresh of the macros which I have previously posted to this site (along with completing a few which never quite got posted).  But first I thought a quick review of some of those new features might be in order.

The biggest change you are going to notice with Alteryx 8.5 is the new interface and horizontal layout.  When I first started using this it felt strange and that is expected.  This is a interface many of us have been using for several years and although it has evolved over those years there have been certain constants from release to release: the tool icons; vertical layout; a vertical toolbox to name a few.  I cannot remember an Alteryx release where the interface has changed so drastically.  So it is going to feel strange.

What surprised me was how quickly I adjusted to the new icons and horizontal layout.  Within a few days I was using the product as efficiently as I had been using the previous release.  But more interestingly I think that in the months since then my efficiency has improved even more than that.  And further, my use of the new interface has also evolved in that time to make use of the different features.

This post looks at some of the new Tool Palette features and the new tool icons.

Tool Categories

The first feature I really like is the tool categories now run horizontally across the screen. Like so

The old Toolbox can be seen to the right.  My favorite thing about the new Tool Palette is that on my 1920 x 1080 resolution monitor I can see all of the categories and all of the tools within those categories without the need for a scroll bar.  On the old one you always had to make a choice of being able to see all of the categories or all of the tools. (The screenshot to the right shows all of the categories.)






 

Search Box

But having said it is great to see all the categories something I have always struggled with is remembering which tools are in which categories.  The times I have searched in a couple of categories looking for a particular tool only to give up and go for the All Tools.  Which brings us to the next new feature: the Search Box.

If I want the Dynamic Rename tool, but can't remember its category, then I can start typing its name in the search box and the tools shown filter down as I type.  So in this example by the time I have typed the first two letters I am down to five tools and can easily pick out the one I want.

Favorites

Another small but really nice feature is how easy it is to put tools in and out of your favorites category.  Each tool now has a small star in the top right corner of it's icon in the tool palette:

Clicking that small star to make it gold adds that tool to your favorites, clicking it again removes it.  This allows you to quickly build up a collection of the tools you use the most and speed up your module building.

Tool Tips

These are great for when you can't quite remember which tool you were looking for.  So back to looking for the Dynamic Rename tool above.  You know it is called dynamic something so you type dy in the box and bring up the five tools we saw above.  Now if you can't remember if it was Dynamic Rename you needed or Dynamic Replace; hovering your mouse over the tool icon in the palette (or if you are impatient like me, double clicking it) brings up a brief description of what that tool does

and you can quickly see that yes it was Dynamic Rename I was after not Dynamic Replace.

Standardization of Tool Icons

The 8.5 release completely refreshes the tool icons and for many of us long time users this was a big change and perhaps one of the most scary.  We had been working with these icons for several years and could recognize each of them by sight like old friends.  By comparison the new icons have a rather uniform look to them, each category of tools has a unique shape and color consistent across the whole category and I think for many of us there was an initial feeling of how can we tell these tools apart?

But look closely and each tool is just as unique as it was before and just as identifiable.  They are still our old friends they just have new uniforms.

New Blog on the Block

Well…  It has been a rather long time since I last posted on here…  Hopefully that is going to change.  But in the meantime if you are eager for some more Alteryx related blogging content, there is a new blog in town by Alteryx’s very own CTO Ned Harding.  (http://alteryxned.wordpress.com).  There has already been a number of great Alteryx related posts and also some more personal posts on teaching kids to program.

Alteryx highlight’s include:

• Reading XLSX Directly
• Ensuring fields are in a data stream (great for macro authoring)
• Dot Density Maps
• Weighted Median

And the August monthly round up is a great place to start too.

There is also a blog post about creating this rather beautiful satellite night style map (above right) showing UK demographic data.

Which brings me very nicely to a more personal announcement.  As long time followers of this blog will recall, I moved from the UK to Boulder, Colorado to work for Alteryx as a software developer.  Well I have now relocated back to the UK mainly because of this next picture.

No not "Big Data Analytics For Dummies".  (Although it is a great little publication and well worth a read.  If you haven’t already downloaded it already you can get a copy here).  But rather the model in the shot: my daughter Ida.  My wife and I thought with it being our first child it might be helpful to have free baby sitting, in the form of Grandparents, a little closer than a nine hour flight away!  So I am back in the UK, but very thankfully still working for Alteryx (I mean where else would I want to work?) and currently living in Cambridge:  Silicon valley of the UK as I like to tell my American friends.  (For anyone who has visited Silicon Valley and Cambridge, I think you will agree that there really is no need for further comment on the subject).

So if anyone feels like talking Alteryx or data (Or just joining some data minded people for a beer) you will find me a regular at the Data Insight’s meet ups in Cambridge http://www.meetup.com/Data-Insights-Cambridge/ (I say regular.  I’ve been to one and it was interesting enough that I’m going to the next one).

Look out for a post soon on the new layout features of Alteryx 8.5, which was rather current six months ago when I first wrote it and not so much now…  But hey it’s almost finished already and it gets me off to a rolling start…

Till then

Adam