Tuesday, 11 January 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.

Friday, 7 January 2011

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 011013021¶024¶025

Returns 01021¶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.

Tuesday, 4 January 2011

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.