Friday 8 August 2014

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.

Friday 1 August 2014

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.