Moving Summarize

This is a tool I have been meaning to write for a long time.  It was requested at Inspire this year and I have had requests for it before that.

The basic premise is the ability to create a moving average (or in fact any moving summarize method).  For background on what a moving average is and why it is useful check out the excellent Wikipedia article on the subject http://en.wikipedia.org/wiki/Moving_average.

In simple terms a moving average creates for each row in a data-set the average of the last x number of rows.  The easiest way to do this without my new macro is to use a multi-row formula and write a formula to calculate the average.  Which works fine until you want a moving average of the last 30 rows for example, where upon writing the formula becomes a little tedious.

If you want to jump straight to the macro you can download it here.  If you are interested in how it works read on.

The basic premise of my macro is to use a summarize tool to calculate the moving summaries. Obvious huh?

So if we imagine a simple dataset with 9 records and we want to do a moving average of the previous 3 rows, we attach a RecordId and use a formula to group the records into groups of 3:

Ceil([RecordID]/3)

Then we can use a summarize tool grouping on the "Grouping formula" field and we have calculated our moving averages!

Wasn't that easy?  Well... OK... Those of you who were paying attention might be saying to yourself "wait a minute we've only calculated three of the answers!" and you would be right.  To be precise we have calculated the answer for rows 3, 6, and 9.  So how are we going to calculate the others?

Well this is when the macro gets interesting. We are going to use a batch macro to do that same process again and again until we have calculated all of our answers.  But of course we can't just run the exact same process or we'd just keep calculating the exact same results!  So each time we go round we "throw away" the first n records where n is the iteration number we are on - 1.

So second time round the loop.  Iteration 2 we throw away the first record and repeat:

Now we have calculated the answer for rows 4 and 7.  We also calculated a number that we didn't need (the 29.66667) but we will discard that at the end.

Then one last time round the loop.  Iteration 3 so discard the first 2 records an repeat:

Gives us the answer for rows 5 and 8 and we have all the answers we need.  Rows 1 and 2 don't have an answer as there are not three previous rows to summarize.

Then all we need to do is get all of the answers back together which we can do using this formula to map our new "Grouping formula" field back to our original RecordIds:

[Grouping formula]*3+[Engine.IterationNumber]

The great thing about this solution is it works as well for moving summarizes of 50 records as it does for 3 and also will work for any summarize method.  Moving Concatenate? Moving Create Centroid?

The final solution is then slightly more complex as I decided to give the user the ability to choose how many rows forwards and backwards they want to include in their moving summarize.  But that is just a case of working out how big a moving summarize we need to calculate and then appropriately offsetting the solution.

This macro will be included in the 2014 Q4 release of the CReW macro pack which should be out early next year or download it here now.

Blog Macro Pack - 2014 Q3 Release

Can you believe it has been three months since Inspire 2014 already?  Which means it is time for the next release of the Blog Macro pack!

It is available for download now at

http://Downloads.ChaosReignsWithin.com/Macros.zip

For installation and support information please read the original post from June http://www.chaosreignswithin.com/2014/06/blog-macro-pack.html

I had thought that after the first collection of macros I released at Inspire that I would be adding perhaps two or three macros per quarter, but this release sees another nine new macros!  So let me give you a brief introduction to the newcomers:

The Runner Macros

These are probably the most exciting of the new macros in that they are a complete new category of tools.  The premise behind them is that you might have a number of Alteryx modules that you want to run in sequence.  These tools allow you to write an Alteryx module which runs other Alteryx modules.

They come in two styles.  First we have the Runner and Conditional Runner:

The Runner macro has a single configuration value which is the path to the module you want it to run.  It runs the module and if the run completes without errors, outputs the log to the Success output.  If there are errors then it outputs the log to the Failure output.

The Conditional Runner works in much the same manner, except that it has an input.  The conditional runner will run its module exactly one time if it receives any input data.  If it received no data it will not run its module.

This means using the two in combination you can run a series of modules where the next module in the chain only runs on success of the previous.  Additionally you could use email tools to notify you in case of a failure:

The second style is the List Runner macro.  This one will take a list of Alteryx modules as a data input and run them one after the other.  It also has a check box to enable the process to stop if a module errors. It outputs all of the logs to a single output with a field indicating success or failure.

Useful if you have a directory of modules and you want to run all of them:

.

The last tool in this new category is the Log Parser.  The above three tools all output the Alteryx log in a single cell.  The log parser tool takes this cell and parses it into an Alteryx data table:

These four new tools are all brand new and somewhat experimental, so if you run into any issues with them or have any ideas for improvements then please let me know.  I am sure there will be a few more versions of them yet!  Feel free to add comments here or join the conversation we have going over on the Alteryx Community site:

http://community.alteryx.com/Troubleshooting/Streaming-Modules-to-run-in-sequence

Community Inspired Macros

Which leads me into the next two macros which were directly inspired by ideas posted on the community site.

The Field Sort tool will sort the fields on the data stream alphabetically as requested here http://community.alteryx.com/Common-Use-Cases/Sort-Field-Names-Tool

The Dynamic Metadata tool will take metadata from another data-stream as requested here http://community.alteryx.com/Developer/Dynamic-Rename-Additional-Functions

Inspiring Ingenuity

Alteryx's CTO Ned Harding's blog http://inspiringingenuity.net/ saw two new macros this quarter which again I have adopted into the macro pack.

The Wildcard Input macro opens multiple files which have differing data schemas using the wildcard syntax.  Check out the full details here http://inspiringingenuity.net/2014/06/27/alteryx-wildcard-inputs/

The Skip Last N records is another community inspired tool and will skip the last N records in a data-stream much like the sample tool will skip the first N.  Again check out the full story here http://inspiringingenuity.net/2014/07/15/alteryx-skip-last-n-records/

Only Unique

Which brings me to the last of our nine new macros the Only Unique macro.  I blogged about this back in August http://www.chaosreignswithin.com/2014/08/only-unique-macro.html.  It works very much like the unique tool except that if there are duplicates then ALL of the duplicate records come out of the duplicate output (rather than one of them coming out of the unique output).

That wraps up the new tools for this release.  As always it is great to hear people's feedback so any thoughts/comments/improvements just let me know in the comments below.

Field Sort Macro

The below recently appeared on the Alteryx Community Ideas Forum:

"In my environment, creating tables with fields in a particular order is a requirement.

The Select tool (and others) let me manually change sort orders. The Select tool will also allow me to sort on Field Name in Ascending or Descending order.

I can add a sort order prefix to my input data with the Dynamic Rename tool, connect a Select tool and then go into the Select tool properties to sort by Field Name. But this requires that I first run the module so that the Select tool knows what the incoming fields are called, then set up the Select tool properties, and then rerun the module. If I change the prefixes or the list of incoming variables, the Select tool has to be reconfigured.

It would be really nice if there was a Field Sort tool that only had Ascending or Descending as properties that would 'dynamically' do what the Sort on Field Name option in the Select tool does."

It turns out that with a little inventive use of existing tools it is possible to do this today as a macro!

As with last weeks post if you want to challenge your Alteryx macro building skills then have a go at building this yourself before looking how I did it.

If you just want the macro to use or learn from, then you can download it here.

Only Unique Macro

While adding "All" and "None" buttons to the unique tool a couple of weeks ago, I was reminded of a macro which I have found useful on occasion but not yet shared on this blog.  The Only Unique Macro:

pictured here with the standard Unique Tool.  

The Unique Tool is great for ensuring that a set of fields is unique, however when you have a set of fields you expected to be unique but aren't, working out why is a little tricky.  

The reason for this is say you have five identical duplicate records (on the selected fields) and you want to look at all of those records together (perhaps to see what is in the other fields), one of the five records is in the unique output stream and the other four are in the duplicates output stream.  If you have a million other records in the unique output stream, finding that one can be hard.

Enter the "Only Unique" macro.  The working of which is as follows:

• If there is exactly one record with a combination of the selected field values it comes out of the unique stream. So the unique output only contains the completely unique records.
• If there are records with duplicates of the selected field values, then ALL of those records come out of the duplicate stream.

You can download the macro and above sample module here, and look out for this macro in the next release of the Chaos Reigns Within (CReW) macro pack.

If you want to challenge yourself, then try to build the described macro without looking how I did it.  I suspect there are probably a lot of different approaches you could take.

I hope you find it useful.

Blog Macro Pack

I am pleased to announce the release of the first version of the Blog macro pack for Alteryx.

Downloadable now at

http://Downloads.ChaosReignsWithin.com/Macros.zip

This is an installable collection of 15 macros collected from the three unofficial Alteryx blogs, with contributions from myself, Ned Harding and Chris Love.  If you are not already following their blogs then please check them out below:

• www.InspiringIngenuity.net
• www.SciolisticRamblings.wordpress.com

Installation Instructions

Alteryx 9.0 is required to use these macros.

1. Download the zip file above
2. Extract it to a location on your machine.  (Preferably not in the Program Files/Alteryx folder)
3. Run Install.yxwz

Community Supported

As with all content on this blog these macros are not an official Alteryx release and as such they have not been through the full testing process that tools in the product have.  Some of them are prototypes and experimental, but that's what some of what makes them so fun.  

Although we make all the effort we can to make theses fully functional macros I am sure there are some bugs in there somewhere.  But having said that if you find an issue let me know either in the comments below or drop me personally a note and I'll take a look, or if you can see how to fix it yourself then even better!  Drop me a fixed version and I will include it in the next release.

Also if you have any ideas for future macros or improvements to the ones we have then again let me know!

Wireless Connections

One of the new features in Alteryx 9.0 is the ability to make connections "wireless".  A wireless connection is invisible on the canvas and is useful for making your modules look tidier.

A wireless connection is indicated by the "radio waves" icon on the connection.

1 bar indicates 1 wireless connection; 2 bars indicate 2 wireless connection and 3 bars indicate 3 or more.

Click on a tool with wireless connections and the wireless connections become visible so you can see where they go:

Click on another tool or the canvas and the wireless connections disappear again.

How to Make Connections Wireless

There are a number of ways to make a connection wireless:

1. Right click on a connection and select "Make Connection Wireless".
2. Connections now have a properties window!  Just select a connection and it appears in the usual properties window just like a tool.  This is where you can rename your connection; see the fields flowing through it and there is also a check-box for making it wireless.
3. Right click on a tool and select "Make Incoming/Outgoing Connections Wireless"
4. From the new Navigation properties window (more on this in a minute) check the Wireless check-box.

To get a wireless connection "wired" again you can use options 3 and 4.

The Navigation Properties Window

Another new feature of Alteryx 9.0 is the the Navigation Properties window we mentioned in method 3 above.  This is a useful new feature even when you are not working with wireless connection.

It is accessible from the "cross-hair" icon on the properties window toolbar (right) and shows a list of all tools connected to the currently selected tool:

Doubling clicking on any of the tools in the list selects that tool and if that tool is off screen moves the screen so you can see the newly selected tool.

As mentioned above you also have the ability here to make the connections wireless or wired.

When to Use Wireless Connections

Having being using the new product for a few months these are my initial thoughts on using wireless connections and how I am using them currently. 

1. As sparingly as possible - A big strength of an Alteryx work flow is being able to see what it is doing from a "mile high view".  The more wireless connections you have the harder this will be.
2. When the two tools are far apart - A connection that goes off the screen is not very useful, so this is a potential candidate for a wireless connection.  But don't forget that you can zoom out for an overview so maybe you do want that wired connection after all?
3. When your connections are "tangled" - Sometimes if you have a lot of connections in one area it can be hard to see what goes where, this means from an overview you can't see what is going on, so keep the important ones wired and make the others wireless to clear the view.

I would love to hear if people agree/disagree with the above points or have any more they would add.  I welcome your thoughts in the comments section below.

Alteryx 9.0 is released!

April 24th saw the release of Alteryx 9.0.  Which is absolutely jam packed with fantastic new features and tools!  So many features in fact, that I'm not going to be able to cover all of them in a single article, but I will give you a brief tour of some of the highlights:

New Tools

This release sees a massive 38 new tools!  Helped in no small part by the new Analytic App authoring process.

Here they all are:

App and Macro Authoring Redesign

The App and Macro Authoring process has seen a complete overhaul this release and is one of my favorite new features.  The process is no longer hidden away on multiple properties tabs, but built up on the canvas using the new "Interface" tools.  Look out for more on this topic in a future blog post.

New Data Formats

As ever there are even more data formats supported this release including:

• SAS data files (.sas7bdat)
• SPSS data files (.sav)
• Output to QlikView data files (.qvx)
• Read and write to Marketo
• Read Google Analytics data

There are also four new social media connectors:

• Twitter
• Foursquare
• Gnip
• DataSift

Improved Download Tool

The download tool has had a lot of new features added this release and now should let you interface with any web API out there.

This is how the four new social media tools (which are in fact macros) work.  So if you want to see an example of the new download functionality in action, open up one of the social media macros and take a look.

Private Cloud Server

The private cloud version of Alteryx has seen a huge update this release.  Now you can get the functionality of the Alteryx public gallery on your own private server inside your firewall.  A fantastic way to deliver Analytic Applications and insights to your wider business.



That is a very quick round up of just some of the new features.  Look out for some more detailed blog posts on specific features over the coming weeks.

Conway's Game of Life Gosper glider gun - Running inside an Alteryx browse tool!

Gauge Macro

Gauge.yxmc



Download
Help

This is actually another old macro, but one which I never actually published on this blog.  It did however make a guest appearance in the Presidential Election App over on the Alteryx Public Gallery.

What Does it Do?

Well in that App it produces this part of the final report:

Showing the percentage of Republican or Democrat support for a given geographical area.  In general it produces a gauge giving a visual representation of a percentage field.  To use it you just add the macro to your canvas and give it some input data which contains a percentage field between 0 and 100.  For each row of your data it will generate a gauge visually showing the percentage.

You can also choose how big you want the gauge to be and customize the colors.

How Does it Work?

Now how it works it a little more interesting.  Does it use the work Ned has been doing on HTML visualizations? No.  Does it use the Run Command tool to call some external application to draw the gauge?  No.  It doesn't need to; we already have a tool in Alteryx which will let you draw things: The Report Map tool.  That's right the gauge and the arrow are actually a map!  Obviously not a map of anywhere physically, but made up of spatial objects created and manipulated by Alteryx and rendered by the Report Map tool.

Creating the spatial objects is the fun part and called upon some long ago learned trigonometry from my school days.  I'm not going to go into the exact details here, the macro is commented so download it and take a look at the details.

But I think you'll agree this opens up a whole world of creating custom reporting controls.  We are only limited by our spatial skills in Alteryx.

Improvements

One thing which could be better in the macro is the way you can choose colors.  At the moment you have to enter a hex format like #ff00ff.  Wouldn't it be great if there was a color picker question type that we could use in macros and apps?  How could I tell Alteryx what a great idea I think that would be I hear you ask?

Well that and any other great ideas that you have for product improvements to Alteryx can be submitted to the Alteryx Idea Center over at the Alteryx Community Site. (I would submit the idea myself, but feature requests carry more weight if they come from real customers ;-) )  But there are already some great ideas over there, so go join in the conversation!