SAS Transpose

Last week I visited a client who had been trialling Alteryx.  The team who had been conducting the trial were SAS analysts and were particularly impressed by Alteryx's spatial capabilities.  At the end of the meeting I asked if there was anything which they thought they could do in SAS and not in Alteryx.  The reply was the only thing was that the transpose tool didn't really to seem to behave as they expected given their experience of SAS's Proc Transpose.

So this weekend I thought I would take a look at what the differences might be.  Now it has been a long time since I have written anything in SAS, so first step was a quick google to find out how the Proc Transpose tool worked.  I came across this excellent tutorial:

http://support.sas.com/resources/papers/proceedings09/060-2009.pdf

I decided I would recreate the lessons from this document using Alteryx.  Upon starting I realised why they had been having trouble, the Proc Transpose function of SAS is closer to an Alteryx cross tab than transpose,  but in actual fact to reproduce the results exactly Alteryx needs a transpose and cross tab tool. 

Just to be clear it is not that Alteryx tools aren't as efficient as SAS they are just different and I have added an extra example to each lesson which I have titled "The Alteryx Way" which shows what I think an Alteryx analyst would do to transform the example data.

I could have tried to turn my modules into a SAS Proc Transpose macro for Alteryx however I don't believe that would be the most efficient way of doing things in Alteryx.  I am not presenting these examples as the best way of doing things in Alteryx; more of a way for SAS analysts to get a handle on the differences between the two tools.  I think if you are going to program efficiently in any language you need to think about your solution in terms of how that language works.

My example modules can be downloaded below:



As always feel free to post any comments/questions here or e-mail me directly.

Alteryx Team Blog

The Alteryx team blog has a new home and name.  Check out the "Alteryx Engine Works" here:

http://blogs.alteryx.com/engineworks/default.aspx

Comments are also working at last, so hopefully we'll see some questions from the community on the new features of Alteryx 6.0 as the team blog about them.  First post on Document Constants is already there and looks like a powerful new feature.

For anyone who is subscribed to my google RSS Alteryx bundle then I have updated this to include the new "Alteryx All Blogs RSS Feed".  For anyone who isn't subscribed then the below link gives you access to all of the Alteryx RSS feeds in one place:

https://www.google.com/reader/bundle/user/08686630194161749278/bundle/Alteryx

On a related note the UK Alteryx Blog now has e-mail subscriptions available courtesy of feedburner.  Blog posts delivered directly to your inbox. Sign up at the below link:

Subscribe to The UK Alteryx Blog by Email

Palette Importer and Exporter

Seeing this post http://srcgroup.extendthereach.com/showthread.php?t=1414 by Margarita on the Alteryx forum reminded me of an Alteryx module I had started a while ago to try and solve a problem with theming on a map.  The solution didn't quite work out as I had hoped, but I believe the module may still be of use.

So here is my palette importer module for Alteryx:



It allows you to add palettes from Expression Design Swatch xml file format (or entered manually in a text input) to your Alteryx reportsettings.xml file.  The reason I chose this format is that I found this site http://www.colourlovers.com/palettes which has over a million palettes to download in this format (though frustratingly they all only seem to have a maximum of five colors, if anyone knows of any better sites please let me know).  You need to sign up to download them, but then just hit the design button under download options:

The module will pick up any number of palette xml files from the same folder that it is in.  I have included two as samples.  Once the module has run then you will need to restart Alteryx in order to be able to see the new palettes.

As Margarita suggests in her post please backup ReportSettings.xml before you start using this.  It is tested, but just in case there are any problems you will want to be able to restore your backup.  The other thing to mention is that if you import a palette with the same name as an existing one then the existing one will be overwritten.

It allows you to relatively quickly find a palette you like; import it into Alteryx and use it:

I have also put together this module:



which exports your palettes from reportsettings.xml to Expression Design Swatch xml file format files.  Ideal if you want to move a palette from one machine to another or share it on the web.

As always any comments/suggestions please let me know.

Gantt Charts

The below recently appeared on the Alteryx forum:

Hi,
I have a an Alteryx module that reports project data and milestones for various projects. What is missing is a visual representation of the milestones and I was wondering if anyone had used the Alteryx reporting tools to create a Gantt Chart type view of milestones/tasks.
Thanks,
Gavin

Hello Gavin,

We do not currently offer a Gantt Chart option in the Chart tool. Thank you for expressing an interest in this feature, we will look into it as a possibility for the future.

-Brett

While Brett's reply is quite correct; there is nothing to stop you creating your own Gantt Chart using the tools currently available in Alteryx 5.0.  If anyone remembers the techniques which were used to create a polar graph or a chess board which used the mapping tool as a custom renderer, then a similar technique could be used to create a Gantt chart.

So I've put together a sample module which shows a possible method for doing this. Which produces the below:

From the following data table:

The module uses a couple of interesting techinques including using the Generate Rows tool to create a data row for every day between two date ranges and generating the spatial objects needed to build up the Gantt charts by dynamically creating json objects (Thanks Ned).

The module looks like this:

and can be downloaded from here:

The things that need doing next with this are to take a look at the scaling aspect (it currently doesn't look so good for timeframes longer than a month) and turn the whole thing into a macro which outputs a report .

As always any questions/comments please post below or feel free to contact me directly.

Add Total Row and Column

Ever needed to add Row and Column totals to your data tables in Alteryx?  This macro makes it easy!

From this:

 

To this:

With this:

Download the macro here:

Tips and Tricks - The Browse Tool

The humble browse tool is probably the most used tool in the Alteryx toolbox, it is a great way to take a look at your data at any point in your module; but how many of you use more than its default settings?  Score yourself out of ten for how many of the following tips and tricks you already knew about the browse tool.

1.  You can select which columns you want to look at and the module will remember these selections:

Great for when you need to check two fields against each other.

2. The browse tool will tell you if a data cell has leading spaces; trailing spaces or embedded newlines by displaying a little red triangle in the corner of the cell.  Hovering over the red triangle displays the message:

3. The "Cell Viewer" button lets you examine the contents of an individual cell and with "Show Whitespace"
turned on shows you all of those leading/trailing spaces and embedded newlines:

4. The cell viewer pane also gives information on fields and rows by clicking on the header/row label cells:

5. It is possible to split the browse tool vertically or horizontally.  Great for comparing records in different rows of the file:

or viewing spatial data in map and tabular form:

6. In the split map/data table view.  Selecting a data row in the table will highlight that spatial object on the map and selecting a spatial object on the map will highlight the corresponding data row.

7. Need to compare records from two different browses in the same module?  No problem! The new window button will create a new floating browse window of the current data; then switch the original properties window to the other browse and compare away.

8. You can save records (all or a selection) directly from the browse tool.  Useful when you have spotted an issue and want to send the records to someone or do some further analysis elsewhere.

9. You can copy records from the browse tool and paste them directly into a text input tool.  Really useful if you need to take a few records into a second module to some extra analysis:

10. The browse tool will display up to 1 billion rows.  After that it only displays alternate rows, but maintains the correct row numbers in the first column.

So how many did you score out of ten?  Post your scores in the comments below and let me know if you think of any others I have missed.

Alteryx Customer Advisory e-Forum (Part 2)

It was great to see such a good attendance for this; I thought it was a really interesting event.  Thanks to all the other presenters for sharing what they are doing with Alteryx.

As promised here are my slides:



Looking forward to the next one!