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!

5 thoughts on “How to Increment in Alteryx

  1. I don’t post to these things often but I had to thank you for putting this together. I just ran into this issue and didn’t skip a beat with your thoughtfully explained post.

    Like

    1. The Record ID works well in most cases, but it doesn’t let you specify the starting value (it always starts at 1) If you’re preparing data to load into some other table that already has an ID field and you want to make sure your new IDs sequentially fit into the other table, then you have to use the work-around described here.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s