We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

alteryx Community

# Alteryx Designer Discussions

SOLVED

## Multi-Row formula for Multiple columns at the same time

7 - Meteor

Hi,

Lets say I have 10 columns and I need to use Multi-Row formula to replicate the values in multiple rows for each of the columns , Is there a way to do it one shot. Or we have to use Multi Row formula 10 times in the workflow? for each column

8 REPLIES 8
8 - Asteroid

You could transpose your 10 columns, update them with one then cross tab it back out but this will make your multi row formula more complicated

cheers

chris

7 - Meteor

Thanks! but as you outlined, formula becomes more complicated. Just to elaborate with an example - The Problem is on the left and expected result on the right. How to achieve this using Multi-Row formula only once and not thrice in the workflow? (since the below example has 3 columns that needs repetition)

 Problem Expected Result Name Col1 Col2 Col3 Name Col1 Col2 Col3 N1 Monthly Quarterly Annual N1 Monthly Quarterly Annual N1 N1 Monthly Quarterly Annual N1 N1 Monthly Quarterly Annual N2 Quarterly Annual Monthly N2 Quarterly Annual Monthly N2 N2 Quarterly Annual Monthly N3 Annual Monthly Quarterly N3 Annual Monthly Quarterly N3 N3 Annual Monthly Quarterly N3 N3 Annual Monthly Quarterly N4 Monthly Quarterly Annual N4 Monthly Quarterly Annual N4 N4 Monthly Quarterly Annual N4 N4 Monthly Quarterly Annual
18 - Pollux

@clant has the right idea

If your formulas are similar enough, you can get away a multirow formula that is not to complex.  The trick here is to keep track of the original row and column orders in the transposed data.

1. Before you transpose, generate a unique row number[RowNumber]
2. Transpose to give you name and value column using [RowNumber] as the key
3. Immediately after, use a multirow formula to fill in sequential values for each transposed row [ColumnNumber], grouped by [RowNumber].  This gives you the fields to sort by to return to your original row/column order.
4. Then sort by [Name] and [RowNumber].  This gives you all the values that were in one column original following each other in the name/value pair of columns
5. Apply your multiRow formula to calculate the new values, make sure to group by Name.
6. Sort your values by [RowNumber] and [ColumnNumber] to get the original order
7. Crosstab to get the original rows and columns back.  You may need to rename your columns after this since the Crosstab tool, replaces spaces in column names with  "_".

The attached workflow applies the formula, if the previous number is even, then change the sign on the current number to both input columns

Hope this helps

Edit after seeing your response:  This approach will fit your data, since the 3 multi-rows apply exactly the same formula, i.e. fill down of the previous non-null  value.  There is still the trade off between the complexity of the transpose-sort-multirow-crosstab approach and simply using X multi-row formulas.  For 3 column its just easier to do each one individually.  At some point though(10 Columns maybe) the transpose approach is just easier to maintain

Dan

7 - Meteor

Thank you, great solution.

However I have  50+ columns, so need to weigh in 50+ Multi-row formula for each column or the transpose approach

15 - Aurora

Hey @girisri

Check out this macro created by @patrick_digan. I think it can accomplish what you're looking for with one tool.

7 - Meteor

Thanks!

7 - Meteor

Based on the you'r expected result from the Input ..The following will helpful.

Thank you

8 - Asteroid

Hi Dan,

I have similar issue issue please help me to fix. i would like to consider all columns mentioned below. Attached workflow(v 2018.3) i'm considering period column only.

Thanks,

Sunitha

Labels