How to Increment in Alteryx

While Alteryx has many great functions right out of the box, I realized one thing they’re lacking is an increment function (or at least I haven’t found one).  Anyone who’s worked with SQL is familiar with the Identity data type that allows you to specify a seed and an increment value as follows:

IDENTITY [ ( seed , increment ) ]

This allows you to create a column that automatically generates new, unique integers by incrementing the starting “seed” value.

Occasionally when loading text-based keys or natural keys into a normalized database it’s helpful to be able to generate incremented IDs.  Below is a snapshot of how I was able to accomplish this in Alteryx using the multi-row function.

The first data input is simply an excel file with a column of text values (item descriptions) as seen below.

The second input labeled “Seed Value” is simply a text input box where I entered a seed value of 10 which is the starting number I would like to increment from. (Note in most real-world applications this seed value would come from some other source, such as a database table, etc.)

The “append field” tool simply joins these two streams of data together (essentially accomplishing the same thing as a cartesian join in SQL) resulting in the following stream:

The final thing we need to do is increment the ID column so that instead of repeating the seed value each row, it increments up for every row.  And here’s where the nifty little Multi-Row formula comes in to play.

All you have to do is set it to update the existing Increment_ID field, and make the value = [Row-1:Increment_ID] + 1 .  Essentially we’re just telling it to make each row in that field equal to the value of the preceding row plus one.  Of course this works great until you get to the very first row which has no predecessor…”Whatcha gonna do now??”  Thankfully alteryx thought about this and supplied an option for “Values for Rows that don’t Exist”.  Simply set it to choose the value of the closest valid row, and since the closest row to row zero is row one which happens to hold our seed value, it will begin incrementing from that number up.

The final result?  Our nicely incremented ID field starting one number above our seed value.  This resulting data stream can now be passed back into a database or sent wherever you want it to go.

So overall it’s a fairly simple workaround.  It could be packaged as a macro for re-use later, or maybe…just maybe…alteryx will read this post, listen to my request and add a handy little increment function for future release!