community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.

Tool Mastery | Multi-Row Formula

Quasar
Multi-Row Formula.png

This article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Multi-Row Formula Tool on our way to mastering the Alteryx Designer:

 

The Multi-Row Formula Tool functions much like the normal Formula Tool but adds the ability to reference multiple rows of data within one expression. Say, for example, someone was on the ground floor of a house and had a Formula Tool. They would only be able to talk to the people also on the ground floor. If they had a Multi-Row Formula Tool, though, they would also be able to talk to the people upstairs, in the attic, and in the basement as well.

 

 1.jpg

 

The Multi-Row Formula Tool allows the user to either update an existing field or create a new one. Both options can be very helpful, and there are many use cases for each. The use cases we detail below are attached in the v11.0 workflow Multi-Row.yxmd.

 

2.png

 

Update Existing Field

 

  • “Filling down” a field:

Often times, data can be arranged in a way that would look good in Excel but is not realistic to work with in Alteryx. In these instances, you might want to “fill down” a column that has missing values.

  

3.png

 

In this case, only the first person in each family has their family name populated. Therefore, we would like to modify the Family field where it is currently Null(). This can easily be accomplished like so:

 

4.png

 

To easily get Variables and Functions into the Expression box, simply double click them from their corresponding tab in the Configuration.

 

Create New Field:

  • Create Unique ID for Each Person In Group:

Now that the data is cleaned up, we want to add a unique ID to each person according to the family they belong to. In order to accomplish this, we will configure the Multi-Row Formula Tool as follows:

 

5.png

 

When creating a new field, the user has the ability to select what field type to give it. Here, Int 16 was chosen. Note that we are grouping by Family because we want the ID to be unique within each family, but it can start over with different families. This new ID field could be used later as an identifier with tools like Filter, Summarize, and more.

 

6.png

 

  • Make Calculations:

What is a good Multi-Row Tool without the ability to reference more than just the rows directly before or directly after it? By changing the value in Num Rows in the Configuration, you are able to increase the number of rows you can use in your expressions.

 

In this example, we increased the Num Rows to 2. Notice how there are now more variables to select from in the middle section on the Configuration. Because we want the running total by family, we again group by this field.

 

7.png

 

This method can be extended to generate running totals in many different scenarios including monetary sales, item counts, and more.

 

9.png

  

Now we want to calculate the average amount of total screen time by family. In this Multi-Row Formula Tool Expression box, we write:

  

iif([ID]=3,average([Row-2:Total Screen Time],[Row-1:Total Screen Time],[Total Screen Time]),iif([ID]=2,average([Row-1:Total Screen Time],[Total Screen Time],[Row+1:Total Screen Time]),average([Row+2:Total Screen Time],[Row+1:Total Screen Time],[Total Screen Time])))

 

This checks each row’s ID value and customizes the formula to generate an average by using the correct rows.

 

10.png

  

  • Difference From Previous:

The last part of the configuration for this tool that has not yet been changed is the Values for Rows that don’t Exist drop down. This tells Alteryx what to use when a formula needs to use the value of a row that doesn’t exist (hence the name of the drop down). This would happen, for example, when calculating a value for the first row of data when the expression contains a Row-1 variable. The user can choose what that non-existent row value is.

 

In this example, the row that doesn’t exist is set to the closest valid row. That way, when the formula tries to use [Row -1: Total Screen Time] on row 1 and sees that it doesn’t exist, it uses row 1’s value instead. This results in the difference of 0 that you see in the output.

 

 11.png

 

A Sort was placed before this Multi-Row Formula Tool to sort Total Screen Time in descending order. The above formula created a field that is the difference between the current row’s total screen time and the previous row’s total screen time. This makes it easy to see how much more screen time each person needs in order to catch up to the person ahead of them.

 

12.png

 

Many times the Multi-Row Formula Tool will be used in combination with the Cross Tab Tool. This is particularly helpful if you are trying to transform a couple columns of data into a table. One member of the Alteryx Community had this exact problem and was able to get it solved in this post.

 

The Multi-Row Formula Tool can only update one field per tool instance. If you would like to update multiple fields (and you feel comfortable with both the Multi-Row Formula Tool and the Multi-Field Formula Tool), try out this macro that was posted on the public Alteryx Gallery.

 

By now, you should have expert-level proficiency with the Multi-Row Formula Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.

 

Stay tuned with our latest posts every #ToolTuesday by following @alteryx on Twitter! If you want to master all the Designer tools, consider subscribing for email notifications.

Comments
Alteryx Partner
Hi BarnesK, Can I ask you about record ID using multi row formula, can I start the first row with another number rather then 1?
Aurora
Aurora

Great examples!

Quasar

@Syarifhidayat Yes! For example, this expression starts at the number 2:

 

 

iif([Row-1:ID]+1=1,[Row-1:ID]+2,[Row-1:ID]+1)

Another way to do this would be to use the regular [Row-1:ID]+1 expression then add a separate Formula tool that just adds 1 to each ID number. Hope this helps!Smiley Happy

 

Thanks for this post and information on how to create an ID field.   This was a useful crosswalk for the SQL rank function and easier to create without worrying about the partition syntax.   Also, this helps with the lag function that I remember from my SAS history.  Either way, this is easier.

Meteoroid

This is very helpful. I was struggling with adding ID based on Business Name and this tool really solved my problem.

Quasar

Thank you for the feedback! I'm glad this post has been helpful! Smiley Happy

Meteor

Hi,

 

i dont see any multi row tool for indatabase objects?

is that true if yes then do i need to stream data out of db and use normal multi row?

 

pls clear this.

 

BR

Brij

Meteoroid

This article was very helpful, but what about a multi row formula that's dynamic across multiple rows?

 

For example, someone is in a company for 10 years and we want to set years 2-10 to the value that is present for year 1, which in this case is the earliest or lowest year? There could be different values in years 2-10?

 

Additionally, there could be some people who are only there for 2-6 years for example. Every person has a unique ID for each iteration of the year. 

Quasar

Hey @tomunger848, thanks for the question. I would sort your data first then use the Group By feature in the Multi-Row Formula tool.  See the below photos for an example I made up. If you need additional help for a specific use case you have, I would suggest adding a new topic in one of the discussion boards.

 

mr 1.PNGInput and overview

 

mr 2.PNGSort configuration

 

mr 3.PNGMulti-row configuration and output

Asteroid

Is there a way to refer to the last preceding row with a non-null value?

Quasar

What is your use case @DHB? Could you 'fill down' like in the example above?

Asteroid

 I managed to find a solution to this problem which I posted here.  I had to filter out the rows with a null value in order to make the  Multi-Row formula work before Unioning those rows back in and sorting them back into the right order.

Meteoroid

Hi , can any explain the logic behind the

 

Row -1

Row +0

Row +1

 

How it be consider in the Expression window.

 

 

Thank-you.

 

 

Quasar

Hi @dondapati

 

The Multi-Row Formula tool goes row by row for the field you selected and performs the statement you type in the Expression window. In this expression, you can refer to different rows of data. This differs from the normal Formula tool in that the normal Formula tool can only look at the current row's values.

 

In your expression, using "Row -1" will look at the field's value in the row just above the current row. "Row +0" is the active row and will look at the value for the row currently being evaluated. "Row +1" references the value in the row just after the current row.

 

For example, say Alteryx is running through the rows and performing the expression in a Multi-Row Formula tool. Say it is currently evaluating row 20. If the expression contains a field referencing Row -1, it will take the value from that field in row 19, Row +0 will take the value from row 20, and Row +1 will take the value from row 21.

 

By using the Num Rows toggle, you can change the amount of rows away from the current that you can reference in your expression.

 

Hope this helps clear things up for you!

Meteoroid

by the way, is it just me or did anyone notice the Game of Thrones theme in the data?

awesome!

Atom

Hi,

 

I have an identical problem to the Filling Down example for the Family column, where I want to replace nulls with the valid value above. I have used the same IIf statement, but the logic only works for the first row after a valid value. (i.e. the second null remains unchanged etc.). Is there a setting I need to amend so that the fill down works for more than one row?

 

Formula used: IIF(IsNull([BusArea]),[Row-1:BusArea],[BusArea])

 

Note: The formula gives an error if I try to update the existing field (The field "" is not contained in the record), so I have had to create a new field.

 

Regards

 

Jason

Quasar

Hey @jnew8

 

Based just on the formula you provided, it seems as though you should be getting the correct result. I would check your selection for the 'Values for Rows that don't Exist' drop down. Also, are you grouping by any fields that might affect your results?

 

If you check these things and you are still having troubles, you can either post a screenshot of your workflow with the input data and the Multi-Row Formula tool configuration here, send me a private message with that screenshot, or post a new discussion in the Designer discussion board with an attachment to your actual workflow so I can download it and run it on my machine. If you post a new discussion, you can tag my name if you want, and I will see it right away to get back with you.

 

Hope this helps!

Atom

Hi BarnesK,

 

Thanks for responding to my question. I have partially resolved the issue myself, as I sent the Alteryx file to a colleague and he ran it on his machine without an error and having made no changes to the code.  So it appears there might be a problem with my installation? I am about to get a new pc with a new version of Alteryx, so once I have the new pc I will try out the code again and see what happens.

 

Regards

 

Jason