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

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

Multi-Row formula for Multiple columns at the same time

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

 

girisri
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  
NameCol1Col2 Col3  NameCol1Col2 Col3
N1MonthlyQuarterlyAnnual  N1MonthlyQuarterlyAnnual
N1     N1MonthlyQuarterlyAnnual
N1     N1MonthlyQuarterlyAnnual
N2QuarterlyAnnualMonthly  N2QuarterlyAnnualMonthly
N2     N2QuarterlyAnnualMonthly
N3AnnualMonthly Quarterly  N3AnnualMonthly Quarterly
N3     N3AnnualMonthly Quarterly
N3     N3AnnualMonthly Quarterly
N4MonthlyQuarterlyAnnual  N4MonthlyQuarterlyAnnual
N4     N4MonthlyQuarterlyAnnual
N4     N4MonthlyQuarterlyAnnual
          
danilang
18 - Pollux
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

 

Solution.png

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

 

 

girisri
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

Kenda
15 - Aurora
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. 

girisri
7 - Meteor

Thanks! 

dondapati
7 - Meteor

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

 

Thank you

 

 

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

 

Sunitha_0-1594887591999.png

 

 

Thanks,

Sunitha

Labels