Sorry for the lack of posts the last few weeks – I have been busy few weeks heads down working on a futures project (code named LockIn.)  I went as far as turning off email and IM to get some real focus.  It was very productive –  I haven’t produced that much code in a while.

Anyway, this week I have a very quick post answering a question from the Alteryx forums.  The questions asks:  How do I skip the last N records from a data stream.  Skipping the 1st N is very easy, just use the sample tool, but it doesn’t have a mode to skip the last N.

The Solution

One of the suggestions is to use the Dynamic Input tool with a SQL query.  While this certainly could work for Excel, it wouldn’t work for other types of data inputs (like CSV), so its not a general solution.

Fortunately, solving it with only basic tools is fairly easy – but it does take a few tools.  I used a Summarize tool to get the record count and and Append Fields tools to append the record count to every record.  After that a RecordID tool adds the record # to each record and its a simple filter to remove the records we don’t need.  Finally a Select tool to clean up the temporary fields that I used.  Note that I named the temporary fields with a complex name so they are unlikely to interfere with field names already in the stream.

With the 9.0 macro authoring tools, it was super easy to make the number of records to skip dynamic.  All it took was dropping in a Numeric Up/Down tool and hooking it up the the filter.  I really love how the new app authoring tools worked out.  Huge kudos to our Desktop Designer development team.

If you need to skip the last N records on a stream, you know how to do it, or you can go ahead and download the macro here.

Thanks for reading,

Ned.