Modulo: How to increment a number every n rows (Alteryx)

by Henry Mak

As described in my previous blog post, the method used is an application of the modulo operator.

One use case where you might want to increment a number every n rows might be when your data needs to be grouped together. One example is in the case of coordinates which may not have the latitude and longitude in separate columns.

In this case, how would you pair up the latitude and longitude together? What we want is to pair these coordinates together like below.

The method for achieving this is with a Multi-Formula tool and a Record ID tool (the formula tool is there for the sake of clarity in the following explanation):

In order to pair these coordinates we have to type the following Expression into the Multi-Row formula tool:

This leaves us with the paired coordinates – woo!

What the Multi-Row Formula does in this case is that checks if the modulo of the record ID, with the divisor as 2, equals 1 (see third column). If this is true, it increases the pair number by 1. If it's not then it stays as the same pair number.

You can extend this method by changing the number of rows in each group, simply change the 2 in the multi-row formula tool into another number:

In this example, for Field1, we have three distinct groups. Each group has 5 rows in.

Note: One problem with this method is that it is inflexible so if your data does not follow a strict structure e.g. if there is a group of 3 followed by a group of 5 and then a group of 8 then this method will not work.

Link to all of my other blog posts on the Modulo operation

Tableau Public