Tool Mastery
Explore a diverse compilation of articles that take an in-depth look at Designer tools.Tool Mastery | Multi-Row Formula
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.
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.
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.
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:
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:
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.
- 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.
This method can be extended to generate running totals in many different scenarios including monetary sales, item counts, and more.
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.
- 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.
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.
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 -
@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!
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.
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.
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.
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!
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:
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).
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.
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.
Hi Kenda,
I have the below dataset, I want the difference between two periods for a particular part no. based on its type, company and division (e.g. 201902 vs 201901 so on and so forth),is there a way I can do this with the help of Multi Rows formula tool?
Thanking you in Advance,
Priyanka
Hi there @psinghania
Depending on how you would like your output to look, there are two different ways to approach this.
If you want to use the Multi-Row Formula tool, you could sort your data first by Part No and Period then use the Multi-Row Formula tool do find the difference. You'll want to group by type, company, and division. This would look as follows:
On the other hand, you could cross tab your data first so that your amounts for each type, company, part no, div combo are in one row and each period is in a different field. From there, you could use a normal Formula tool to create a field to calculate the difference.
I hope this is helpful!
Hello all, I am attempting to run compares on massive CSV files with 300+ columns. An example I am trying to do below.
I'm attempting the tool you mentioned above, As you can see there will always be Two "IDS" the same, so I'm trying to compare the same two ID's on specific fields such as Originator, the problem is when I'm doing record zero and +1, it's not really accurate when the data is spitting out because after the initial first row, it will be comparing two ID's that aren't the same, I need logic so it first determines if the ID's are the same then "analyze" those two rows and then write the "reason" to the new column for both of those ID's that it analyzed, any help would be appreciated!!! I'm stuck.
