December Enigma - Solution

and the answer is the 1st and 2nd of October '25.  You can find my solution here:


Interestingly the other two solutions I have seen from other people, both look quite different to my solution.  But that is one of the huge strengths of Alteryx: there is never just one way of solving a problem!

December Enigma

With the next Alteryx Grand Prix just around the corner I thought I would offer up another enigma for you all to  test your Alteryx skills on:

http://www.newscientist.com/article/mg21228341.400-enigma-number-1668.html

This particular puzzle will test your knowledge of the Alteryx date time functions.

If you enjoy a challenge and want to test your skills why not think about signing up for the Grand Prix.  Entry is currently open and full details can be found here.  Check out last year's winner Jason Dunkel's blog on his experience here for some hints, tips and inspiration.

Look out for the solution next week.

Optimisation Quiz Answer

Well the answer is that module B is faster.  That is to say adding an extra sort actually speeds things up. So using my sample data whose counts and sizes you can see in the images:  Module A runs in about 45 seconds while Module B runs in less than 30 seconds.

Which kind of seems counter intuitive, how does adding more processing make things run faster overall?

Well a great place to start looking for optimisations in an Alteryx module is to look at where you are sorting data.  A sort is a pretty resource intensive task, so the less you can do it the quicker your module will run.

In module A, behind the scenes Alteryx is sorting in two places:

• The summarise needs to sort the data in order to do the group by on the grouping field.
• The batch macro also needs to sort the data to be able to batch it into chunks.

So module A has two sorts.

But if sorts are bad for run time, then how can adding an extra sort speed things up I hear you ask?  

Well that's the clever bit...  When a tool in Alteryx sorts some data it tells the tools down stream of it that the data is sorted and what it is sorted by.

So back to our example:  We noted above that module A was performing two sorts, but actually it was doing the same sort (by GroupingField) twice.  By adding a sort in where we did the data gets sorted once there.  When the data gets to the summarise tool and the batch macro tool, it is already sorted by the GroupingField and does not need to be sorted again.  Rather than adding a sort in we have actually reduced the number of sorts the module needs to do by one, thus gaining the saving in the run time that we see.

Optimisation Quiz

A Friday quiz for you to test your Alteryx skills. This is based on a real world module from a colleague at Alteryx which I was looking at earlier this week, and is a question about optimisation.

I have isolated the particular section of the module which we were looking at and have two versions: Module A and Module B. The question is simple: which module is faster, if either, and why?

I've set up a poll on the right side of the site for you to cast your votes and will post the answer and explanation on Monday.

So the 2 modules look like this (I flipped a coin to decide which was A or B so you can't to read anything into the labeling):

Module A

Module B

The modules are identical apart from that single sort. So effectively the question is does that sort make things faster, slower or make no difference?

The input data is unsorted with about 10M records in my example (obviously any optimisation makes more of a difference when there is more work to be done. The purpose of the module is there is a large number of records with a grouping field attached (in this example there are 50 groups) which need to be run through a process in batches.

The summarise works out how many groups there are and feeds that into the control parameter of a batch macro. The batch macro then uses the GroupBy feature to read records from its input in batches based on that control parameter. My example module just writes those records to a file, but for the question it does not matter what the batch process actually does, the answer is the same.

So vote for your answer on the poll on the right and look out for the solution on Monday.

Alteryx 7.0 - New Features Announced To Date

As I mentioned in my last post I think Alteryx 7.0 is going to be the most exciting release that I have seen (and that's not just because I have been working on it!)

I thought it would be worth a brief re-cap of the new features announced so far (complete with links to the Alteryx Engine Works blog):


Guzzler Improvements (link)
Some improvements to the core drive time engine in Alteryx's functionality offering: "which allows for more accurate drive time and drive radius calculations"

XML Parsing (link)
A feature that I know users have been asking for, for a long time.  Alteryx 7.0 sees XML support in the input tool along with a new XML parsing tool.

Alteryx web and the Private Cloud (link)
Alteryx 6.2 saw the release of Alteryx web On-Premise solution, another hugely exciting development for Alteryx. "It is an installable web application that allows end users to upload Alteryx wizards, manage users and run wizards all via a web browser." And being On-Premise clients can keep all of their data and modules secure on their on servers within their company.  Alteryx 7.0 promises more exciting features around "Permissions, Scheduling, Active Directory and viewing yxdbs and PCXML files."

Alteryx Map Changes (link)
This one is close to my heart as it is the main feature I have been working on since I joined Alteryx last February.  It is actually the second major re-write the mapping tool has seen and hopefully fulfills many of the requests we have received for mapping.

Input/Output Enhancements (link)
A new split button and latest used files gives you faster access to your files and database connections.  Plus the new Alias feature to manage your data connections and passwords.

Chaos Reigns Within

Well it has been a rather long time since I have last posted here.  What can I say?  I've been busy.

But I am back; and with a few changes.

First one is we have a name change.  "UK Alteryx User" just wasn't quite right: I'm not in the UK anymore and I'm not really a user anymore.  So I am proud to announce the new name "Chaos Reigns Within" and the new URL www.chaosreignswithin.com (don't worry all the old links should redirect just fine).  If you know what the new name means, then congratulations you can call yourself an advanced Alteryx user.

Other changes you will notice are a restyle of the site and a legal disclaimer, which I can't say I really like, but that's the world we live in.

Other than that I'm planning to post more regularly here and am looking forward to the release of Alteryx 7.0 next year which I think is going to the most exciting and feature packed release since I have been using the tool.

Solving Sudokus with Alteryx?

For today's post there has been a slight change of author, Adam suggested I blog about this as it fits in fairly well with some of his unusual applications of Alteryx.

So I guess I should introduce myself to start with, some of you will know me but I guess some of you won’t. Having the same surname as Adam isn’t a coincidence, we’re married and have been for about 4 years now. We both use Alteryx in our working lives and although I don’t quite think about things in the same way as Adam (not sure too many people do!) I also like to see and test what Alteryx is capable of.

A while ago Adam and I tried to solve a puzzle from the New Scientist magazine using Alteryx. The puzzle revolved around being able to solve a Sudoku puzzle. There was obviously slightly more to it than just a Sudoku puzzle but being able to solve the Sudoku was a necessary part of it. We started thinking surely we can do that in Alteryx, Adam in his ever confident fashion, Alteryx can do anything! We soon realised it wasn’t really going to work out, a major problem being that we needed to be able to loop through our methodology. This was several months ago, and recently it come up again. Would we now be able to solve sudoku’s using Alteryx; with the latest version that includes iterative macros it seems like it might be possible!

So below is my attempt to make Alteryx solve sudokus, it does work; but only on ‘simple’ puzzles.



As it turns out there is a lot of methodology behind solving sudokus and although I think it would be possible to write a module which would solve any puzzle (that is solvable) I haven’t actually implemented all the different techniques (yet!). During my investigations on this topic I have come across this website which appears to detail all the different methods needed for solving a Sudoku, there are quite a few and I haven’t read them all but the ones I have seem to be explained quite thoroughly. www.sudokuwiki.org/sudoku.htm

My module uses an iterative macro so that it can loop round and solve cells and then run through again and hopefully solve more, until it either has managed to solve the Sudoku or gets to a point where it is never going to be able to solve it. (If it can't solve at least one cell per run through then it is not making progress and will never be able to solve it, hence the iterative macro is limited to 80 runs through.) Although it would have been nice to be able to produce something which can solve any puzzle, at the moment this is the best I’ve got, I may add to it over time but in the mean time it has been a good learning experience for getting to know iterative macros. (And it’s given my brain a work out trying to
logically think how to go about solving a Sudoku in Alteryx!)

Hope you’ve enjoyed this somewhat pointless blog post and if anyone else wants to have a guest spot on Adam’s UK Alteryx blog then just get in touch with him.

Easter Eggs

Not the chocolate type, but the hidden game type.  Did you know that Alteryx has two?

If you want to find them then the About screen should be your starting point.

Happy Hunting!

A Spatial Challenge - Part 2

So here is my module:



It works by creating 5 internal buffer polygons of decreasing size and then setting decreasing opacity to provide the fade effect.

It has been pointed out since I wrote this that it is also possible to use negative buffers, which would be another way to create the internal polygons.

How I set the colors so that no adjacent counties have the same color is a post for another day...

A Spatial Challenge

There is a shop on Pearl street in Boulder which sells antique maps. While I was browsing through their stock on Saturday and reminding myself I really don't need to buy an antique map for $100, I spotted a rather neat way of coloring polygons. Which it turns out is a reasonably interesting challenge to reproduce in Alteryx. The below is my reproduction from Alteryx, complete with aerial photographs to bring it a bit more up to date than the antique version I've copied (it shows the counties of Colorado if you were wondering). 

If you feel like giving your Alteryx spatial skills a workout then have a go at creating the effect yourself.  I'll post my module later this week once I have had time to annotate it.

Inspiring Fractals

I wanted to start this post by saying how great it was to see everyone at Inspire 2011. I thought it was an amazing conference and really enjoyed seeing what everyone has been doing with Alteryx over the last year.

This module was inspired by a particularly interesting conversation I had with a client at the closing reception, where we were talking about the new location optimizer macro type and then got on to talking about Fractals.  Which in turn was the inspiration for this module.

A forewarning to those of you who thought this years Grand Prix questions didn't have a direct business application, you might not appreciate this one.  For those of you who like to see the edges of what Alteryx can do then I present the Mandelbrot set produced by Alteryx.

It is actually a great example of how the iterative macro works and though lacking a "direct" business application a good learning example of this new feature.  The other point of interest is I have used the map tool with 1,000,000 size 1 points effectively as a "screen" to plot my results on (I'm sure there's a business use for this idea somewhere...).

If you want to read more about the Mandelbrot set.  Wikipedia has a good article, including the algorithm I have reproduced in Atleryx. 

New Beginnings

A slightly different post to the usual content of this blog.  More of an announcement perhaps.

On Monday this week (a year and a day since I started this blog) I started a new job; as a software developer in Boulder, CO.  Working on (you guessed it) Alteryx Engine.

I feel like I should be saying I'm sorry that this blog won't be the independent voice on Alteryx that it once was.  But who am I kidding?  I have loved this product since the day I started using it and I started this blog to share ideas and push the boundaries of what can be done with the tool.  And in that respect things aren't going to change much round here.

The move to Alteryx was in many ways a natural one, I think Alteryx is an amazing product and I want to be part of developing it and making it even better.  As always I welcome your comments, feedback and ideas; and hope to see many of you in a little over a week at Inspire 2011.

Alteryx 2011 - New Features - New Charting functionality

Alteryx 2011 brings a brand new shiny charting tool. And very nice it is too!  It has a host of new chart types and a range of new options.  I'm not going to go through all the options in detail here, but instead give you a brief preview of some charts I have produced with it.

It currently sits in the Laboratory section of the ToolBox which according to the help file is for "new tools that have documented, known issues or are not feature complete.  They have been tested for stability and will be optimized in stable program updates".  Though I have to say I didn't come across any issues while creating the below samples.

Also check out the Alteryx blog on Box and Whisker Charts here.

Alteryx 2011 - New Features - Tree Question in Wizard/Macros

Alteryx 2011 brings a great new feature to the wizard or macro creator's arsenal: the Tree question type.  It allows you to present a selection of options to the user in a hierarchical structure.

It is added in the same way as any other question:

Data Source

It has five options in terms of choosing its source data:

• Allocate Geography
• Allocate Variables
• File System Directory
• Custom XML
• Custom File/Database

Allocate geography and variables will display tree views of the allocate geographies or variables from the allocate data sets on a users machine:

File System Directory will display a tree view of files in a folder (Note: Only Files are selectable):

Custom XML lets you define your own tree using a XML file.  The XML needs to have the following structure:

The n attributes are the display names and the k attributes are the key values which get passed to your module.  The v elements define the tree structure or hierarchy.

Which results in the following tree structure:

The final option allows population of the tree from a custom file or database.  The structure needs to be like the below:

This gives the same tree as with my custom XML example above. 

Note: There is an important difference between using a file/database source and a XML source.  In an XML source you define the tree structure using the v elements; the names and keys can be whatever you want.  In a file/database source it is the key values which define the tree structure; so in this example the fact that 0 is a substring of 01 means that Delaware sits under USA in the tree structure.

Additional Options

There are two additional options to set up.

• A check box which determines whether a single or multiple items can be selected.
• A numeric value which determines how many lines the tree will display before needing to scroll.

Return Values

If you are using an allocate data source for your tree there are two options for the return data, selectable from the question's properties:

• Save Values as XML - returns values in an XML format suitable for updating allocate tools using an "update raw XML" action.
• Save Values as Allocate Workspace snippet - does what it says and can be used to update allocate tools via an "update/change value" action.

If you have a File System Directory data source the selected files are returned with full paths separated by new line characters.  This could be used to update a dynamic input using a text to columns tool as in the below example:

When you get to a custom data source things begin to get a little more challenging for the macro/wizard developer.  The difficulty is that the question will return the minimum possible data to represent the options selected and does not return any indication of hierarchy.  So still using my custom XML example above:


Returns 011¶013¶021¶024¶025

Returns 01¶021¶024¶025

Returns 0

Now if you have data at County level, which you wish to filter based on the user's selection how do you deal with these different cases?

The best solution I have at the moment is a filter updating with an "update/change value" action using "Update using a formula" with the below formula:

'[Key] in ("' + REGEX_Replace([Custom XML Tree View], '\n', '","') + '")' +
' or left([Key],2) in ("' + REGEX_Replace([Custom XML Tree View], '\n', '","') + '")' +
' or left([Key],1) in ("' + REGEX_Replace([Custom XML Tree View], '\n', '","') + '")'
 
The REGEX_Replace([Custom XML Tree View], '\n', '","') part converts the newline separated list to a comma separated quote qualified list and the three "in" statements check at the three levels of the hierarchy.

As always if you have a better solution or any other comments feel free to post below.

Alteryx 2011 - New Features - New Spatial Functions

Alteryx 2011 adds two new spatial functions to the formula toolbox:

• ST_CreateLine(point1, point2, ...)
• ST_CreatePolygon(obj1, obj2, ...)

Which pretty much do what they say.

So ST_CreateLine(ST_CreatePoint(1,1), ST_CreatePoint(1,2), ST_CreatePoint(2,2),  ST_CreatePoint(2,1)) gives us a spatial line:

and ST_CreatePolygon(ST_CreatePoint(1,1), ST_CreatePoint(1,2), ST_CreatePoint(2,2), ST_CreatePoint(2,1)) gives us a polygon:

Great for creating your own charts (e.g. Gannt or Polar) or other interesting uses of Alteryx's spatial functions.