Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Modulo (mod) Function: Not just for Evens and Odds

CristonS
Alteryx Alumni (Retired)
Created

You may have seen the modulo (mod) function used in the awesome Collatz Conjecture macro on the Gallery; here, it's used to determine the parity of the entered integer. Depending on if the number is even or odd, a different set of operations is applied to the value.

 

 

Given two positive integers, n (the dividend) and d (the divisor), n modulo d (or n mod d) is the remainder of dividing n by d. For example, the expression "3 mod 2" would return a value of 1 because 3 divided by 2 leaves a quotient of 1 and a remainder of 1, while "6 mod 3" returns 0 because the division of 6 by 3 has a quotient of 2 and leaves a remainder of 0. To test if a number is odd, you’d want to determine when a number (n) divided by 2 (d) is equal to 1. For even numbers, the result is equal to 0.

 

 

The mod function can be found in the Formula tool’s Math functions, nested under the sub-set of Integer functions.

 

 

 

2019-02-28_16-40-21.png

 

 

2019-02-28_16-38-20.png

 

The function, however, has many uses beyond determining if a value is even or odd. When coupled with a Record ID tool, the modulo function can be used to make groups within data. Keep reading for three examples of how to apply the mod function!

 

 

Example 1: let’s say you’ve received a dataset of geographic coordinates with the latitude and longitude for each different sites split into separate records.

 

 

You have this: You want this:

 

 

2019-02-28_16-43-05.png

 

That the latitude and longitudes are stacked allows you to know that the first two records are the coordinates for Site 1, the third and fourth records are the coordinates for Site 2, and so on. You run through your options for grouping records: “The Tile tool? Handy, but this option won’t work I don’t know how many groups I have in my data. A conditional statement in the Formula tool to flag positive latitude and negative longitude values? Possible, but this definitely won’t work if my points are outside of the western hemisphere”. For a dynamic solution, consider the one-two punch of the Record ID and the Formula tool’s mod function!

 

 

Adding a Record ID provides a field for establishing a pattern. Since we know the data are in pairs or multiples of 2, the Record ID associated with the odd entries are the first of every pair. So, when the remainder of the Record ID divided by two is one, we know we’re at the first record of a paired group. We can apply this logic in a Multi-Row Formula to determine the groupings of our coordinates.

 

 

2019-02-28_17-00-43.png

 

2019-02-28_17-02-21.png

 

 

2019-02-28_17-05-36.png

 

 

After some data manipulation, the coordinates for each site are in order and ready to be used for spatial analyses.

 

 

Example 2: Similarly, say you have a client file where the names and addresses are all in a single field, rather than discrete fields:

 

 

 

2019-02-28_17-06-44.png

 

 

But you know that each client has four records, and the name is first, address1 is second, address2/ suite is third, address, the last line, is fourth. So the client names are records 1, 5, 9, and so on. Address1 is records 2, 6, 10, etc.

 

 

Use Mod([RowCount], 4) to number the records, and a conditional statement to create the Headers:

 

 

2019-02-28_17-07-28.png

 

 

Finally, transpose to get your fields in order:

 

 

2019-02-28_17-08-21.png

 

 

 

 

Example 3: Imagine that you have an elapsed time in seconds and you want to convert this to hours, minutes, and seconds:

 

 

 

2019-02-28_17-09-03.png

 

The hours are just a fraction – elapsed time divided by 3600 (the number of seconds in an hour). The seconds are the remainder once you've divided the elapsed time by 60, "removing" the full minutes, and the minutes result is the remainder once you've "removed" the full hours.

 

 

2019-02-28_17-09-47.png

 

 

2019-02-28_17-10-29.png

 

 

Fun with math! Happy Alteryx-ing! See the examples above in the attached workflow.

Attachments
Comments
AlexKo
Alteryx Alumni (Retired)

Excellent examples - thanks Criston and Christine!

DataUser
7 - Meteor

Is there an opposite to the MOD function? I'm looking for some function or way to get the quotient integer ( so not including the remainder) like the Quotient function in excel. Is there a Alteryx formula for this or a work around that would yield this result?

AndyM
Alteryx
Alteryx

@DataUser I don't believe there is a single function but it can easily be achieved by using a simple formula:

 

C = Floor( [A] / [B] )

 

where C will contain the integer quotient portion of the division of A and B. 

RyanHao
6 - Meteoroid

very nice method applied to good case!

urezwan
8 - Asteroid

I need some more time to get used to for the multi-row formulas.

 

I would use a combination of IF and Mod function using 4. Something like the Excel's equivalent of IF(MOD(E3,4)=1,1,IF(MOD(E3,4)=2,1,2))

urezwan
8 - Asteroid

@DataUser, (A - Mod(A,B))/B should work as well. A little way around.

p-g
8 - Asteroid

Great Article! However, I discovered that you can pivot the table in Example 1 using a combination of Formula and Cross-Join Tools. After using the Multi-Row Tool, insert another Formula tool with below expression to classify each row as either Latitude or Longitude. You could then use the Cross Tab tool with the following options: Group Data by 'Pairs', 'Change Column Headers' by Label, Values for New Columns by 'Coordinates' and Aggregate values by Last to produce the same results. It would shorten the number of tools required to achieve the end result. Hope this helps!

 

Label = IF Mod([RecordID], 2) = 1 then 'Latitude' else 'Longitude' endif

 

MultiRow_CrossJoin.png