Extend 2010

I would like to start by thanking everyone who was involved with the organisation and running of Extend 2010.  I thought it was a great event and very well organised.  I met lots of interesting people; got to see what others were doing with Alteryx and picked up a few new tips and tricks along the way.  This article is my summary of the event.

Day 1 - So after registration and lunch the first sessions I attended were the Alteryx Product Training.  These were the first part of a series of sessions totalling eight hours covering a range of Alteryx topics.  The first two sessions covered Allocate and Solocast before moving on to the basics of Alteryx.  Not a lot new for me in these sessions, but giving demo's and training in Alteryx is something I do in my role at Experian, so I found it useful to see someone else's approach to training.

The final session on day 1 was the Alteryx User Group Forum.  I thought this was a great session to meet other Alteryx users and see what other people are doing with Alteryx.  Ron House kicked off the session talking about his blog and had kindly given me five minutes to talk about mine.  It was a great opportunity to talk about what I've been doing and why I think interaction among the Alteryx community is important for all of us.  We then had a number of speakers from different companies talking about how they are using Alteryx; an excellent opportunity to get new ideas to take back to our own work.

Day 2 - There were a few different streams running on day 2 including the continuation of the Alteryx Product Training and the DemographicsNow User group, but the one which I attended was the Developer Summit.  This was a series of presentation's by Alteryx employees on a range of interesting topics aimed at the developer user of Alteryx.  All of the material from the presentations can be found on the Alteryx team blog here.

Session 1 - This was an overview by Ned of the tools in the Developer Tools section of the Alteryx toolbox, essential kit for anyone wanting to build macros, wizards or modules which begin to push the limits of what Alteryx can do.  I'd seen and used all of these tools before but was great to get some clarification of exactly what they do behind the scenes.

Session 2 - Another session by Ned this time on how to build Alteryx macros.  A good introduction on how to get started.

Session 3 - This session was run by Rob and covered the new feature of Alteryx 5.0: batch macros.  This is a feature I was crying out for while I was using Alteryx 4.0 and so have used a great deal prior to the conference.  My summary of the feature is that it starts to let you do loops in Alteryx.  Rob presented an interesting demonstration of how they could be used and a real world application.  What I found interesting was that relatively few people in the audience seemed to be using this powerful feature (look out for a future blog post on this one!).

Session 4 - Now we were starting to get into real developer realms with Linda presenting how to run Alteryx wizards from C#.  There's not many presentations you will see with Visual Studio and C# code up on the screen!

Session 5 -  This was a taster by Iain on the new Alteryx Web Services.  This looks like it will be very exciting and I'm looking forward to it being released and hopefully being able to have a play with it.

After lunch there was a 'bonus' session with Ned in the solution centre on using the Alteryx SDK to build your own Alteryx tools.  Another very exciting topic which opens up all kinds of possibilities for using Alteryx (another topic to look out for a blog post on).

Then after lunch we had the Alteryx Grand Prix in which I was a 'driver' and was the reason I had managed to get a ticket to the conference.  It's effectively "build Alteryx modules against the clock" and try and be faster than the other nine competitors.  It was a lot of fun and maybe got my heart racing more than getting up to talk at the user group forum the day before!  Look out for a second article covering this in more detail later.

Day 3 - The day kicked off with the President's Opening from Dean Stoecker where the company name change was announced (see my previous post); followed by an amazingly inspiration talk by Erik Weihenmayer the only blind man to have climbed the highest peak on every continent, including of course Mount Everest.  The rest of the day had a number of speakers from different companies talking about how they were using Alteryx in their businesses.  Though I must admit as a developer I didn't attend all of these, instead spending a fair bit of time in the solutions center talking with the Alteryx product developers.  Something which I found very useful, so thanks everyone for your time.  The other interesting session in the day was a talk by Amy Holland on the future of Alteryx; I'm looking forward to getting my hands on Alteryx 6 already!

The day concluded with the awards dinner and then drinks into the evening, a great end to a great conference.  I found the whole event really useful and motivational, especially being able to meet other developers and exchange ideas and techniques.  I hope I can get there again next year!

The King is dead. Long live the King.

At the Extend 2010 conference on Wednesday Dean Stoecker, then president of SRC, announced that SRC going forwards will just be called Alteryx. So no more SRC, no more www.ExtendTheReach.com, just Alteryx and www.Alteryx.com.  The full announcement can be read here, but from a personal viewpoint I think this is a great move by the company and I think the new branding looks amazing.  I'm excited about what I'm sure is going to be a great year for the company and product.

Extend 2010 before and after the re-branding:

Look out for a more detailed report from the conference later in the week and some blog posts on a couple of new macros written collaboratively at the conference.

RSS Readers (or how to keep up with all the Alteryx news in one place)

It might just be me who is behind the times, but until the end of last year I had never used a RSS reader.  For those of you who don't know what a RSS reader is (or like me it is something you have vaguely heard about but never quite known what the point of one is), it is basically a piece of software (web or windows based) which pulls a number of news feeds together into one place.   Still confused?  Then click on the below link:

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

This is a bundle of Alteryx feeds created using Google's RSS reader (cleverly named Google Reader).  What you see on that page is all the recent posts from my blog; Ron's blog; the official SRC Alteryx blog and questions posted on the SRC forums.  So all you need to do is subscribe to that bundle and you can keep up with all the Alteryx hints, tips and news in one place (http://www.google.com/reader).  And what's more if new Alteryx news feeds become available in the future (or if anyone knows of any now that I am missing) then I will add them to that bundle and as a subscriber you will automatically start receiving those feeds as well.

So what are you waiting for? Subscribe now!

Reading KML Files

On the SRC Alteryx forum this week ashokbhatt asked if Alteryx can read and write KML files (http://srcgroup.extendthereach.com/showthread.php?t=1287).  Co-incidentally I also received an e-mail from a colleague on Friday asking about Alteryx reading GML files.  While the answer to both of these questions is that there is no in-built support for reading or writing these file formats, they are both of course xml documents which can be digested by Alteryx using techniques similar to what I used in part 3 of my posts on RegEx here.


The below module shows how a simple placemark file could be read using Alteryx:





and this one how a simple placemark file could be written:





Obviously these only pick out a very specific section of the kml file, but hopefully will give some ideas of what could be done. 


The difficultly in writing a generic kml reader is that kml is such a rich data format (http://code.google.com/apis/kml/documentation/kmlreference.html) that some of the data stored in it would have little meaning in Alteryx. 

Alteryx Plays Chess

"OK the first thing I have to admit before I start and anyone gets too excited: is my wizard doesn't actually play chess (I haven't got the time to write a chess engine at the moment ;-) )  What it does do is let two users play chess against each other. But with that caveat said, on with the post..."

I'm not quite sure how this particular wizard came into being. I had just read Bill Flanagan's post (here) on using the mapping tool to create a polar graph, which I thought was great; and wanted to have a go myself at creating something custom using the mapping tool.  Turned out I couldn't really think of any pressing business problems that I could put this application of the mapping tool to use on; so just for something to experiment with the concept, I thought I would render a chess board and pieces.  So one lunchtime using the Alteryx spatial tools and some custom chess piece icons I got from the net, I built a simple module which renders a chess board and pieces.

Once I'd got that far I started wondering how difficult would it be to make a wizard which allowed a user to move those pieces around the board?  Turns out it didn't need many more tools to allow a user to enter a start and end co-ordinate in a wizard front end and get the piece to move between those locations.  Which gives us a wizard which allows two people to play chess against each other!

What I haven't done is program Alteryx with the rules of the game: although I think it would be possible to program that logic within Alteryx, that one is going to have to wait until I have more time.  Then the next step of getting the wizard to actually make its own moves in response I think is going to be beyond the abilities of Alteryx (although you could always run an external engine via the command tool...)

But having said that the wizard as it stands is a fun demonstration of what can be done using the mapping tool. It is available for download here:

\0 delimited

Ever wondered what the point of \0 delimited files would be?  Ever wished you could output delimited files with no quote qualifiers?  I had done both and having found the answers thought I would share them here.

Last week I ran into a problem I have had on a few occasions over the years I've worked with Alteryx.  This time round some of the tricks I had used before weren't going to work so I decided to post the problem on the SRC forum.  A big thanks to Margarita at SRC, as the same day I got a reply which let me build a working solution!

My problem is a seemingly simple one to do with the delimited output file which Alteryx produces.  What I wanted to output was a delimited file with a pipe delimiter and no qualifiers.  Alteryx does pipe delimited no problem, but when you come to options on qualifiers all you have is the tick box "Always qualify fields" which is either true or false. But there is no option to "Never qualify fields" which is what I want.

So as you might have guessed from the title of the post the solution comes from using \0 as the delimiter.  What \0 does is produce an output file with no delimiters and no qualifiers, which at first glance might not seem so useful.  But what it allows you to do is build up your output file exactly as you want it using the functionality of Alteryx.

For example if you have a dataset with three fields some of which contain quotes and you want to output a pipe delimited, non-qualified file; then you can just use the formula tool to build a new field like so:

[Field1] + "|" + [Field2] + "|" + [Field3]

and then a select to output only that new field to a \0 delimited file.

The below module shows a few techniques of how you could use this:

RegEx and Alteryx (Part 3)

For those of you who haven't read the comments on part 2, Ned Harding has posted some extra information on how the Tokenise Method works here.  This shows how you can use the tool to split a field on a RegEx matched delimiter as the help file describes.

On with part 3...

Replace Output Method

The last and I think possibly the most powerful of the four methods in the RegEx tool.  For this method I am going to leave the e-mail example and take a look at something I seem to be needing to do more and more recently: xml processing.  So many applications today are outputting xml files or requiring xml input, that being able to process it efficiently needs to be a staple tool of any data developer.  

Before I began to learn about RegEx I struggled to process xml using Alteryx as from what I have seen, there are no specific tools for reading or writing it.

(this the stage that somebody says to me: "why didn't you just use the xxx tool?" and I say because I didn't know it existed. Oh the time I could have saved if I'd only known about that multi-field formula tool a year earlier...) 

I was using complex formulas to search for parts of xml strings and then more formulas to extract the piece of data I wanted.  With RegEx finding and extracting parts of xml suddenly becomes rather easy.

For this example I will use a piece of xml which I hope that anyone who has used Alteryx much will have at least seen, if not manipulated themselves: an Alteryx module.  Yes, for those of you that weren't aware, when you save an Alteryx module it is saved as an xml document which, with due care, can be modified in any text editor.  Great for those times when you need to rename about 100 fields using a file layout you have been supplied in Excel.

This example can be followed in the module RegEx_Example_Part3.yxmd downloadable from here:

The example module uses its own xml as an input and outputs a list of all of the tool ids in the module; along with their types and their x, y display positions.  Run the module and take a look at the output browse, then add a new tool, save the module and notice how the new tool you added is now included in the output.

So how does it work?  Well there are 3 stages to the module:

1. Get the xml into a single field and single row.
2. Use our RegEx replace to extract the required information from the xml.
3. Use Text to Columns tools and a select to format the output.

Now as this article is focusing on the RegEx tools I will leave you to work out how stages 1 and 3 work for yourselves and only explain stage 2.  The module actually uses the RegEx tool with the replace output method 3 times:

The first use is to remove all of the carriage return line feeds from the data:

So \r\n is the Regular Expression for a carriage return line feed combination, we have specified that we will replace it with nothing and we have also specified that we will pass all the other unmatched text through to the output as well.  I think this could be done using the standard formula REPLACE function, but I think you would need to use the CHARFROMINT function and know the unicodes for a carriage return and line feed, which isn't really any easier.

The second use is to remove all white space from between the xml tags:

This time we are searching for one or more white space between two > < characters and replacing the whole lot with ><.  This is another example of something which I believe would be very difficult without RegEx; you could remove all spaces from the data easily enough, but that would remove spaces inside the tags which we wish to keep.  You could also remove a fixed number of spaces between the ends of tags, but to remove an unknown number of spaces I think you would struggle.

The final use, having prepared the xml, is to extract the data elements from the xml corresponding to the Alteryx tools.  

From looking at the data I identified that the block of data I was interested in always looked like this:

<Node ToolID="52"><GuiSettings Plugin="AlteryxBasePluginsGui.BrowseV2.BrowseV2"><Position x="99" y="779" />

which I can quite easily write a regular expression to match like so:

<Node ToolID="(\d+)"><GuiSettings Plugin="([^"]+)"><Position x="(\d+)" y="(\d+)"

notice that I have four marked groups (the parts of the regular expression in parenthesis) representing the parts of the xml data I wish to extract.  The power of a RegEx replace is that I can reference these named groups in the replacement text.  

For example if I made my replacement text $1 then the whole expression would be replaced by 52 (the first marked group) in the above example.

What I choose for my replacement in this example is 

$1¬$2¬\($3,$4\)|

Which for the above example results in 

52 ¬AlteryxBasePluginsGui.BrowseV2.BrowseV2¬\(99, 779\)|

This pulls out the 4 pieces of data I want in a nicely delimited format which I can then split into cells by using the text to columns tool, which is what happens in stage 3 of the module.

I think that the RegEx replace tool is possibly the most useful of the RegEx tools available in Alteryx and definitely worth spending the time getting to understand.

Other RegEx Functions

The last thing to complete our overview of the RegEx capabilities of Alteryx is to briefly mention the remaining two functions available in the formula tool.

REGEX_COUNTMATCHES takes a string and a regular expression and returns the number of times the regular expression matches the string.

REGEX_REPLACE works in a similar manor to the replace method of the RegEx tool, but within the formula tool.  One exciting use of this is in the actions of macros and wizards, an example of which I plan to cover in a future post.

Conclusion

That concludes my overview of the RegEx capabilities of Alteryx, though my examples are just some starting ideas of what is possible.  I hope I have inspired you to learn some basic Regex and next time you are struggling with a complex string manipulation think about using the RegEx toolset.  

If you have any specific questions or comments on anything I have posted about then feel free to e-mail me or post a comment here.