Crew Macro Pack - 2016 Q2 Release

The latest release of the CReW macro pack is available for download now at

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

NOTE: You will require Alteryx 10.0 or later to use this macro pack.

For installation instructions see this page

WHAT'S NEW?

New Macros

Wait A Second - This month I am very excited to be able to introduce another new contributor to the CReW macro pack: Daniel Brun.  Who as well as contributing this macro here has also released his own macro pack "The Inviso macro pack for Alteryx" available for download here.

The Wait A Second macro is a different spin on the throttle tool which allows a user to specify a wait time between records passing through the tool.

Blocking Test Tool - This macro came directly from a client request and is actually something which I have seen asked for many time before.  It is very useful when updating a external data source and you want to check some condition before you write *any* data out.  It checks all of the data against the given expression and only if all of the records pass that expression does it then pass its records downstream.  If any record fails the expression then an error message is thrown and no records are output.

The Test Macros

These are four new macros which were born out of a series of blog posts I wrote back in 2015 about trying test driven development for Alteryx macros.  That experiment actually hit some stumbling blocks which I am yet to write up about, but the macros that were born out of it live on and are very useful for testing in a TDD style or otherwise.

I am releasing them in this release with a BETA flag as they are still very much a work in progress and I know I still have some work to do on them.  However I believe they are in a good enough condition to get some other people looking at them and feeding back any issues and improvements they might find.

They are under their own tool category "CReW Test" which you can hide for now if you want to wait for them to evolve some more.

They are

Test Runner - This is the macro which runs all of your tests.  You generally have a workflow containing only this macro.  Running that workflow will run all of your tests.

Expect Equal - This macro takes two data streams (Expected and Actual) and throws a (hopefully useful) error message if they differ.

This is the macro where I know I have some work to do and where you as a community can help out.  I need any examples you can find where this tool isn't doing what you expect.  So data sets you think are equal, but it thinks are different and vice versa.

Expect Zero - This macro expects to receive zero records into it and if it doesn't throws an error telling you.

Expect Error - This macro is for checking that workflows correctly error.  An essential part of testing.  If it is placed within a tool container then it expects a tool in that same tool container to throw the expected error message.  If the correct error message is not thrown then the Test Runner will mark that test as failed.

I intend a follow up article on how to use the macros, but to get started quickly take a copy of the GettingStartedTemplate folder in TestMacros, rename it to something meaningful and run the contained RunTests.yxmd to run the sample tests.

You can also take a look at the CReW macro test suite which you can find in Samples\CReW Macros\Tests.  Again a work in progress, but the starting of what is to follow.

As always please let me know about any issues/feedback you have and hopefully see many of you at Inspire next week!

(If you are not registered already what are you waiting for? http://www.alteryx.com/inspire-2016)

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.

Another New Alteryx Blog

As if one new Alteryx blog (inspiringingenuity.net) wasn't enough, there is now another Alteryx themed blog by no other than the reigning Alteryx Grand Prix Champion - Chris Love.

Check it out here http://sciolisticramblings.wordpress.com.

His latest blog post creates a bi-variate themed map macro using some great custom XML manipulation.

He also has some great posts on:

• Using Alteryx to compete in Kaggle competitions - http://sciolisticramblings.wordpress.com/2013/10/19/titanic-analysis-little-effort/

• Tableau - http://sciolisticramblings.wordpress.com/2013/10/26/tableau-its-not-excel-what-every-new-user-needs-to-know/; http://sciolisticramblings.wordpress.com/2013/10/27/exploring-food-hygiene-ratings/

• Building Interactive Html Maps - http://sciolisticramblings.wordpress.com/2013/10/26/building-interactive-html-maps-with-r-and-alteryx/

The interactive maps post makes use of some of the experimental work that Ned Harding (Alteryx's CTO) has being doing over on his blog around interactive charting.  If you haven't been following these posts I would highly recommend that you take a look.  He is just wrapping the series up so a great time to read the whole lot at once. http://inspiringingenuity.net/2013/11/11/alteryx-a-reusable-nvd3-macro/

Look out for a macro from me using some of his techniques coming soon...

Happy New Year

So 2012 is finally here and what a great year for Alteryx it is going to be.  We are now less than two months away from the annual conference: Inspire 2012 and the launch of Alteryx 7.0.

Inspire as always promises to be a great learning event, with the chance to see first hand the new Alteryx 7.0 features and learn how you can leverage them in your business.  If you are looking for some reasons to justify to management why you should go, check out the excellent template letter on the Alteryx website here. Remember that Alteryx 7.0 is going to be a huge release packed full of new tools and features so there will be plenty to learn and take back to your company.

If you are already planning on attending, then application is still open for the annual Alteryx Grand Prix.  For more information take a look at the recent post on the Alteryx Engine Works blog here.


Also if you haven't checked it out recently: Ron House's All About Alteryx Blog has some really interesting posts on using Alteryx 7.0 to leverage some online Location-Based APIs.  So head over there and take a look.

(First day back in the office after Christmas break, found this awesome mug waiting for me.  Great way to drink my morning coffee!)