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!

Tool Mastery

Explore a diverse compilation of articles that take an in-depth look at Designer tools.
Become a Tool Master

Learn how you can share your expertise with the Community

LEARN MORE

Tool Mastery | Multi-Row Formula

Kenda
16 - Nebula
16 - Nebula
Created
Multi-Row Formula.png

Thisarticle 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 workflowMulti-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 atcommunity@alteryx.comif you’d like your creative tool uses to be featured in the Tool Mastery Series.

 

Stay tuned with our latest posts every#ToolTuesdayby following@alteryxon Twitter! If you want to master all the Designer tools, considersubscribingfor email notifications.


Additional Information

Click on the corresponding language link below to access this article in another language -

Attachments
Comments
Kenda
16 - Nebula
16 - Nebula

Hi @dberroth 

 

Based on your explanation and your sample provided, it looks like you're on the right track with the Multi-Row Formula tool!

 

Here is what I would recommend - start with one Multi-Row Formula tool to assign an ID row number for each ID group. This way, you can easily identify which row is the first for each ID group. Here is the expression I would use for this:

[Row-1:Group ID]+1

Use this to create a new integer field, and don't forget to Group By the ID field!

 

Kenda_0-1603135042720.png

 

 

Next, add another Multi-Row Formula tool. This is the one that will actually create your REASON CODE field. Here is the expression I would use:

iif([Group ID]=1, iif([Originator]=[Row+1:Originator], "Not Acceptable", "Acceptable"), iif([Row-1:Originator]=[Originator], "Not Acceptable", "Acceptable"))

This first checks if the first record in the group is being processed. If so, the current row and the next row are checked. If any row other than the first is currently being processed, then it will check the current row against the previous one. Again, don't forget to Group By the ID field! This will also need to be a string field.

 

Kenda_1-1603135244773.png

 

Hope this helps!

 

dberroth
8 - Asteroid

Thank you Kendra!!! I knew it was possible just couldn't get all the way there...appreciate it!

joshid
5 - Atom

Would you mind explaining this expression please, IIF (if, then, else), I'm not understanding how it's written here.

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

 

Kenda
16 - Nebula
16 - Nebula

Hey @joshid 

 

I like using the IIF(bool,x,y) function in Alteryx as opposed to the IF bool THEN x ELSEIF bool2 THEN x2 ELSE y ENDIF, but essentially they work the same. 

 

To relate the two types of conditional functions I listed above, I have color coded them here: 

IIF(bool,x,IIF(bool2,x2,y))

IF bool THEN x ELSEIF bool2 THEN x2 ELSE y ENDIF

 

In the simple IIF(bool,x,y) statement, there are 3 pieces of information it needs. First, it needs the conditional expression that you want to check. This expression should return your true or false value. Next, you need to tell Alteryx what you want the value to be if that expression is true. Finally, tell it what to return if that expression is false. 

 

In the example you provided, the expression is first checking if the ID=3 for the current row. If it is, the expression will return the average of the last 3 rows. If it is not (false), it will start a new condition to see if the ID=2. If it is, it will return the average of the last row, the current row, and the next row. Otherwise, if false, it will return the average of the next three rows.

 

Splitting the code out into rows would look like this:

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

 

 

Finally, putting this in the IF bool THEN x ELSEIF bool2 THEN x2 ELSE y ENDIF format would look like this:

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

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

ELSE average([Row+2:Total Screen Time],[Row+1:Total Screen Time],[Total Screen Time]) ENDIF

 

Hopefully some of that was helpful! 

Mehrheyr
6 - Meteoroid

Hi there @Kenda 

I am trying to find max value for each category (group) by date how can I do it with Multi-Row formula?

for example, here I trying to find max price for each date, group by Market, Delivery Month, and Portfolio.

Mehrheyr_0-1652203611948.png

 

Thanks,

 

Kenda
16 - Nebula
16 - Nebula

Hi @Mehrheyr  

 

It sounds like the Summarize tool will be a better fit for your use case here. Check it out!

 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Summarize/ta-p/24944

https://help.alteryx.com/20221/designer/summarize-tool 

Can some one help me with one question, I have many files with i have combine with a batch macro now i have to pick only few row like 289 to 322 row from each file, so my idea is that i will use a multi row formula tool to compare the filename of each row ,

 

like :- if row-1 file name = to row 1(active row) then give number from 1 + and so on and if it don't match then then reset the number to 1 and give 2 , 3 , 4 so on 

 

then finally i will take a range tool to get row which are in 289 to 322 from each file,

 

but i'm not sure about the formula here so can anyone help me please. ? 

Kenda
16 - Nebula
16 - Nebula

@shazaibshoukath1234 You're definitely on the right track! Try this...

iif([Row-1:file]!=[file], 1, [Row-1:New Field]+1)

Yes your correct @kenda I just had to rename New Field name to RecordID,

 

correct formula for other users : - IIF([FileName] = [Row-1:FileName], [Row-1:RecordID] + 1, 1) 

 

Thanks for this community and You.

sadraddinr
5 - Atom

Thank you very much @Kenda for awesome examples.

Regarding the last case, I came up with a different solution. We can simply do a single formula for ID#1 within each Family and then use another simple MultiRow formula to fill down within each Family.

What I mean by within Each Family is to simply Group by Family.

Yes, we will use 2 Multi-Rows but a much simpler workflow, no?

Kenda
16 - Nebula
16 - Nebula

@sadraddinr Thanks for taking the time to read the article and respond! I'm not sure I know exactly what you mean in your post, but as with most problems in Alteryx, there are typically many ways to solve the same problem! That's the great thing about the product. It allows people who approach problems differently to still come to the same conclusion. 

Yogesh_Kalantri
7 - Meteor

Thank you for posting this.. I am still not clear what will happen if we increase # of rows...can someone please help / guide me here. Thanks in advance

Kenda
16 - Nebula
16 - Nebula

Hi @Yogesh_Kalantri thanks for posting your question. If you are referring to the numeric up/down in the configuration of this tool, this allows you basically "access" to that many rows above and below the current row being evaluated. For example, if you have 5 rows of data and Alteryx is currently evaluating row 3, if you have your Num Rows set to 1, in your expression, you can reference rows 2, 3 & 4.

1

2 <- current row -1

3 <- currently being evaluated with the expression you wrote (active row)

4 <- current row +1

5

 

If you increase your Num Rows to 2, you have access to 2 rows above/below the current row being evaluated.

 

Hope this helps!