Subscribe Bookmark RSS Feed

Creating new col with col name

samayash

Community Trekker

Joined:

Jun 24, 2014

Hi,

I have five numerical columns. Need to create a new column reflecting the maximum values in each row of the five column. Instead of the returning the value I need to put the corresponding column name in each row.

Thanks

Sam

2 REPLIES
ms

Super User

Joined:

Jun 23, 2011

It can be done with a column formula but it's easier with a small JSL script. The loop in the last line identifies the column with the highest value and puts its name in the new column "max". If several columns have the same maximum value, the first instance will be picked.

dt = Current Data Table();

cols = dt << getcolumn names(numeric);

newcol = dt << New Column("max", character);

For Each Row(newcol[] = cols[Loc Max(Matrix(cols))] << get name);

ron_horne

Super User

Joined:

Jun 23, 2011

hi Sam,

at the most basic level, try this:

dt = New Table( "data table",

     New Column( "X1", Formula( Random Uniform() ) ),

     New Column( "X2", Formula( Random Uniform() ) ),

     New Column( "X3", Formula( Random Uniform() ) ),

     New Column( "X4", Formula( Random Uniform() ) ),

     New Column( "X5", Formula( Random Uniform() ) ),

     add rows( 100 )

);

dt << New Column( "Maximum Col Name",

     Character,

     formula(

           If(

                :X1 == Maximum( :X1, :X2, :X3, :X4, :X5 ), "X1",

                :X2 == Maximum( :X1, :X2, :X3, :X4, :X5 ), "X2",

                :X3 == Maximum( :X1, :X2, :X3, :X4, :X5 ), "X3",

                :X4 == Maximum( :X1, :X2, :X3, :X4, :X5 ), "X4",

                :X5 == Maximum( :X1, :X2, :X3, :X4, :X5 ), "X5",

           )

     )

);


from this you can generalize it further.

please notice that if there are equal values that are the maximum in one row this formula will prefer the column by his location in the if statement (for good and bad).