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.
One common task most analysts encounter is the need to setup dynamic filters in their data workflows. In SQL this is usually accomplished via a sub-query which is joined in to a main query. In Alteryx, where each step is accomplished via dedicated tools or nodes, the process is similar but much more visual. Usually it involves some combination of the summarize, join, and filter tools.
Here at the TABS Group, we process lots of CPG sales data. Sometimes it comes in a weekly format and we often apply a threshold to remove items which have little to no sales over a two year period. Below is an example how this is easily accomplished via Alteryx.