Create multiple columns in multirow formula.

[davidoc]

Hi,  I have  a multi-row formula that I want to use for more than 1 column - I have  15 different cols that I need to use it on - is there any way that I can achieve without replicating the multi-row too?  I want to create 15 new columns based on each multi row formula.  Any help or pointers appreciated! Dave

[danilang]

Hi @davidoc 

The standard way to approach these types of problems is to add a RecordID and transpose the data so that your 15 columns become a pair of name/value columns.  Apply a single multirow tool to this column, sorting as required beforehand.  Once you have your new result column with the 15 new grouped values, apply a crosstab to this new column and join the results back to your original data set.

Dan

[davidoc]

HI Danitang,

I have used this approach in my workflow  but as I am using a multirow formula  eg creating a new col called criteria 1 Net  based upon:

IF [matchid] = [Row+1:matchid]
THEN ABS( [Sum_criteria1int] - [Row+1:Sum_criteria1int])
ELSE [Row-1:Crit1Net]
ENDIF

there are 15 net values using Criterianint  where n= 1to 15

I am not too sure if record id and transpoie would work well in this example as its multirow

Dave

[danilang · Accepted solution]

hi @davidoc 

The technique is proven and used frequently with multi-row tools.  Can you provide some sample data.  A few rows and several of the columns along with the details of the required calculations.  With this, we'll be able to put together a sample that you can extend to your complete dataset

Dan