Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.

Tool Mastery | Multi-Row Formula

14 - Magnetar
14 - Magnetar
Created
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.

Attachments
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?
16 - Nebula
16 - Nebula

Great examples!

14 - Magnetar
14 - Magnetar

@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

 

7 - Meteor

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.

6 - Meteoroid

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

14 - Magnetar
14 - Magnetar

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

7 - 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

6 - 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. 

14 - Magnetar
14 - Magnetar

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.

 

Input and overviewInput and overview

 

Sort configurationSort configuration

 

Multi-row configuration and outputMulti-row configuration and output

8 - Asteroid

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

14 - Magnetar
14 - Magnetar

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

8 - 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.

7 - Meteor

Hi , can any explain the logic behind the

 

Row -1

Row +0

Row +1

 

How it be consider in the Expression window.

 

 

Thank-you.

 

 

14 - Magnetar
14 - Magnetar

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!

6 - Meteoroid

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

awesome!

5 - 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

14 - Magnetar
14 - Magnetar

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!

5 - 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

8 - Asteroid

I'm trying to fill down a field exactly as shown and am getting this error message,

 

"Error: Multi-Row Formula (5): The field "" is not contained in the record."

 

Here's my formula,

 

iif(isnull([Course]),[Row-1:Course],[Course])

 

Any idea as to what I'm doing wrong here?

 

8 - Asteroid

I've got it now.  You also have to specify the field at the top as well as in the expression.

14 - Magnetar
14 - Magnetar

Hi @DHB 

 

Thanks for reading this article, and I'm glad you were able to solve your issue! If you run into any more problems, definitely reach out and I would be happy to help out. 😊

7 - Meteor

Hi BarnesK,

 

This is a very great article.

 

I have a quick question. I'm doing a bit test on Multi-Row fomula and the result is not what I expected....

I was mean to fill in the Year, but it seems the tool re-arrange the rows firstly then put all 0s in. Did I configure it wrong?

 

Thanks.

 

Input.JPGOutput.JPG

 

 

Alteryx
Alteryx

Hello @Jes_Li , your formula works, you just need to uncheck the Group, by checking Year, the data gets sorted by this field to apply the formula.

7 - Meteor

Hi Margarita, really appreciate your reply. Problem now is solved. Thanks

7 - Meteor

Hi BarnesK,

 

A quick question, is there a way to fill "up" the value like I have below data:

 

 a20
 b30
 c40
CashTotal90
 d200
 e0
Non-cashTotal200

 

And I want to fill the empty becoming this:

Casha20
Cashb30
Cashc40
CashTotal90
Non-cashd200
Non-cashe0
Non-cashTotal200

 

 

7 - Meteor
6 - Meteoroid

Hey Guys,

I want to apply different formula on different rows and the formulas to be used in each successive row contains different column ,how can I parse through different columns one by one on each row ?

 

14 - Magnetar
14 - Magnetar

@gurujo  do you have an example you could share?

6 - Meteoroid

@Kenda

 

SPCGDCPOR-aGDCPOR-bGDCPOR-cGDCIND-aGDCIND-bGDCIND-c
1009923330
2440567040
3712008790
41400120120

 

bl&taskMost used spc for GDC POR

Most used spc for GDC

IND

a41
b33
c2-

 

 

As u can see im trying to find the maximum used Spc from different columns ,for example- GDCPOR-a contains 140 in 4th row and ts spc is 4 .

14 - Magnetar
14 - Magnetar

@gurujo 

 

I have mocked up a workflow for you to get to your desired output. It actually uses no Multi-Row Formula tools. Instead, the key was to transform the data first. Because I can not include attachments to this post, I will provide a screenshot and some details explaining what each tool is doing. If you still have questions, feel free to send me a private message and I can assist you further. You could also post a new discussion in the Designer Discussions section of the community and one of us will be able to help you further there, as well. 

 

Capture.PNG

 

1. Transpose the input data selecting SPC as your Key Column and selecting the rest as Data columns. 

2. Use the Text To Columns tool to split your Name field with - as your delimiter. Only generate 2 columns.

3. Cross Tab your data with SPC & Name2 as the fields you group by, Name1 as your column headers, Value as the Values for new columns, and Sum as the aggregating method.

4. Create two streams of data - one for the IND group and one for the POR group. For each, sort Descending, then keep only the first one with the Sample tool (grouping by Name2). 

5. Modify the SPC field with this expression: 

iif([GDCIND]=0,null(),[SPC])

(use GDCPOR here for the other stream of data)

6. Join both streams by Name2, and only keep Name2 and the 2 SPC fields (renaming with the Join tool as well).

 

 

Hope this helps!

7 - Meteor

Hi Kenda, 

Your advice regarding this tool has been very insightful - I'm trying to pull data into respective columns but am unsure how to go about it. 

In my scenario, I have a data set that is providing 3 different sets of ID's but am unable to pull them out except for the first row in the normal formula tool. I hope the visual below helps with what I am trying to explain. Have you ever run into this type of problem before?

Input Info ABCD
12341234567891011121314
5678    
91011    
121314    

 

A,B,C and D would be the output columns. 

14 - Magnetar
14 - Magnetar

Hi @Ben_Kohler  Just to be clear, do you start out with just the column of Input Info then you want to create columns A, B, C, D that look like the picture you provided? 

7 - Meteor

Hi @Kenda

It's actually being pulled from a text file. I'm able to get the first row easily with a Left (Field_Name, #) pretty easily but 

the additional data shows up directly below the first one. 

 

So in the text file, ill see something like the below:

Acct #                     1234

                               5678

                               91011

 

The problem I'm currently experiencing is that 1234 will appear in my subsequent columns as 1234 instead of what is below. The text file will always have the same format, just different data from day to day, and I've used the multi row formula to "fill in" dates and other info until the info changes, but never like this before. 

14 - Magnetar
14 - Magnetar

I think I know what you're getting at @Ben_Kohler 

 

If I'm understanding correctly, you actually may not need the Multi-Row Formula tool. This solution would make your workflow dynamic enough to handle any amount of input rows that may come in. If your input/output look like this, then this solution should work for you:

InputInputDesired OutputDesired Output

 

First create a RecordID then CrossTab your data using the  RecordID as the headers and the Acct #'s as the values. Next use a Join and join the output of the CrossTab with your text input by record position. Then use a Union to keep the J plus L (here, L is input data) and make sure to sort by a specific order (J input first).

Annotation 2020-03-24 092307.png

 

7 - Meteor

@Kenda 🙂 Thanks!!! I'll give this a try and see how it works out. I hadn't thought of this approach/used the transpose tool very much. Much appreciated. 

7 - Meteor

@Kenda - I've worked this a few times. I don't think this will be my solution unfortunately. It has allowed me to play with the cross tab tool more and learn it's functions. When attempting this, it did exactly what you had explained, however on my end my records were much larger than anticipated. 

 

Upon running my data, the information started to appear as so: (The column headers go all the way into 400+ in this pattern). 

Initial RowABCDEFGHI
11        
2 2       
3  3      
4   4     
5    5    
6     6   
7      7  
8       8 
9        9

 

Unsure if there is a way to grab specific records based on position and then place them into a field similar to the example below? I figure then I could use a formula based on the field.

Initial RowABC
1123
2456
3789
4   
5   
6   
7   
8   
9   

Sorry if this has evolved more than expected. I know I need to work on this some more. 

5 - Atom

I'm having issues where the multi-row tool seems to stop working after ~51,000 rows.  The use case is somewhat intensive as it is looking if sequential row centroid buffers overlap, along with two other conditions to generate an identifier.  

 

Are you aware of any limitations on this tool in terms of maximum rows/conditions/both it can handle?

Thanks for any help.

14 - Magnetar
14 - Magnetar

@apqueen I am not aware of anything like that. Is it consistent where it stops working? Is there any way that you could share your workflow/data so we could troubleshoot?

5 - Atom

@Kenda - I eventually found the issue, which was my field type selection. 

 

To conserve space in my new field I originally set the field type to Int16. 

This worked fine with smaller data sets of about 10000 rows, however when I opened up the inputs (100,000+ rows) this resulted in more outputs than could be handled by Int16 and multirow formula calculated integer values over ~31000 returned Null. 

 

After changing to Int64 my issues went away.

6 - Meteoroid

Hello @Kenda,

 

Is there a way to dynamically select all preceding values in formula?

 

Example: for the third value I want to sum the first and second, the fourth I want to sum the first, second, and third, fifth I want to sum the first, second, third, and fourth, and so on and so forth?

 

Thanks,

Nick

14 - Magnetar
14 - Magnetar

Hey @nickgomez610 

 

For this use case, I think your best bet might be to use the Running Total tool in the Transform category.

6 - Meteoroid

Thanks for the response @Kenda.  

 

I have tried to use the running total tool but the only problem is that I need the cumulative sum I calculated in the prior row to calculate the value in the current row. Therefore, I think I would need to rerun that running total for every row's calculation. Unless is there a way to make the running total dynamically recalculate the total for every row?

14 - Magnetar
14 - Magnetar

@nickgomez610 

 

Yes, the Running Total tool calculates a cumulative sum for each record so all of the previous records will be taken into account. See the second field below.

Kenda_0-1589561320391.png

 

If you want to do this in the Multi-Row Formula tool, you could. Just create a new field with this expression (where New Field is the new field you're creating and Field2 is the field with your existing numeric values you want to add):

[Row-1:New Field]+[Field2]

You get the same output as above. 

 

If neither of these solutions work for you, please provide some sample data and what you would like your output to look like, and I can provide you with more direct assistance. 

5 - Atom

Hi, can I fill a number of different rows simultaneousely? I just have to fill 12 rows and I'm thinking of appliying the formula for all of them at the same time, not just create 12 diferent Multi-Row-Formulas. 

14 - Magnetar
14 - Magnetar

Hey @andreguichard 

 

If you are looking to apply an expression to multiple fields at once using multiple rows, try out this macro created by my friend @patrick_digan 
https://gallery.alteryx.com/#!app/Multi-Row-Multi-Field/59639862effc2a0c04c7e653

5 - Atom

Perfect Kenda. Can you give me some insights on how to use the macro. Is not working for me as I input the data. 

14 - Magnetar
14 - Magnetar

@andreguichard  The syntax would be similar to that in other formula tools using standard functions and variables. This tool has the added row functionality (similar to multi-row), column functionality (new), and currentfield functionality (similar to multi-field). For those pieces, here is an example of how to build the syntax:

[Row+1:FieldName:Column+1]

 

You can leave the Row+1 part or the Column+1 parts blank if you don't need them.