Topic Thursday: Grouped counter

The 13th part of our new series "Topic Thursday" with valuable tips and insights around the topic Alteryx. The articles are written by our colleague Alexander Gross, Alteryx ACE and Business Intelligence Consultant & Alteryx Lead at M2.

In many datasets we need not only one RecordID, but one RecordID per unique value of a column, e.g. if we have the datasets of several countries and want to number them per country. How to do that, I explain in this article.

One possibility would be to build a batch macro with a RecordID tool, but it is also much easier with a multi-row formula. Many beginners first sort the data and then solve this similar to Excel with the following formula:

IF [Row-1:Spalte] != [Spalte] Then 1 Else [Counter] + 1 Endif

In Alteryx this can be done even easier with the grouping function, after that it only needs the following function:

[Row-1:Counter] + 1

Why? With the group function all data is divided into blocks. For example, for a country column, there could be 100 data rows for 'Germany', 200 for 'England' and 300 for 'Switzerland'. Then the formula is executed for each block individually. The default setting for 'Values that don't exist' is already '0 or Empty', so the counter row before our first row of the block is filled with 0 and the current or first row becomes 1. All further rows and blocks are calculated as expected and get their counter exactly as we would expect.