topic Re: How to create a formula column to find the maximum of several columns in Discussions

How to create a formula column to find the maximum of several columns

adampaulsmith — Mon, 03 Feb 2014 22:00:03 GMT

I have several columns of numeric data (see example below) and I would like to create a formula column to find the maximum value for each row across the columns. Any help would be appreciated. Thank you!

Value 1 Value 2 Value 3 Max Value (I would put the formula in this column)

1 9 6 9

2 1 5 5

3 7 4 7

4 2 6 6

Re: How to create a formula column to find the maximum of several columns

pmroz — Wed, 27 Mar 2019 19:33:33 GMT

The Maximum() function will do this for you.

Maximum(:Value 1, :Value 2, :Value 3)

Re: How to create a formula column to find the maximum of several columns

adampaulsmith — Tue, 04 Feb 2014 13:57:57 GMT

Thanks ! I knew it had to be something simple.

Re: How to create a formula column to find the maximum of several columns

NDENNIS418 — Wed, 27 Mar 2019 18:37:26 GMT

If you have many rows which you want to search, is there a function to select a range of rows as opposed to typing in every row into the function?

Re: How to create a formula column to find the maximum of several columns

txnelson — Wed, 27 Mar 2019 20:47:02 GMT

The question asked by @adampaulsmith and answered by @pmroz uses the Maximum() function in a formula define for the resulting column. The formula will automatically loop across each row and apply the formula. Thus, no row reference has to be made.

However, if you are using this in open JSL, you can add a subscript to the column references to indicate which row you process.

For( theRow = 1, theRow <= 10, theRow++,
     :MaxValue[theRow] = Maximum( :Value 1[theRow], :Value 2[theRow], :Value 2[theRow] );
);

See documentation on JSL in the Scripting Guide

Help==>Books==>Scripting Guide

Re: How to create a formula column to find the maximum of several columns

GabeM27 — Fri, 27 Mar 2020 23:36:40 GMT

@txnelson I am having a similar problem. Where I want to get the maximum of multiple columns. In my example I have a list of columns called FreqList. That I created using a formula similar to,

dt = current data table();


FreqList = dt << get column names( string, numeric );

For( i = N Items( FreqList ), i >= 1, i--,
If( Contains( FreqList[i], "Unique" )== 0,
FreqList= Remove( FreqList, i, 1 ),
Contains( FreqList[i], "Name" )== 0,
FreqList = Remove( FreqList, i, 1 )
)
);

// Then  I was thinking something like the following should work,

dt << New Column ("Freq", numeric, Formula(Col Max(FreqList)));

The manual way of me doing it would be to create a new column called "Freq". Then creating a formula like Maximum(:Freq1,:Freq2,:Freq3,FreqN),which would create a new column that has the max freq based on the row.

How would I do this in JSL?

Re: How to create a formula column to find the maximum of several columns

txnelson — Sat, 28 Mar 2020 02:22:22 GMT

The syntax you made up for your example will not work, however, what I have gleaned from it, is that you want to create a new column that contains the maximum value across a list of columns. The code specified in a formula, such as

:a * 2 + :b

must be input as the actual literal string one wants for the formula. JMP will not do any evaluation of the JSL in the formula before it is pass on to the new column being created. Therefore, the formula one wants to use, must be fully realized. And in your code, it needs to be dynamic, which requires the formula statement to be created in JSL and then passed to the formula.

Below is a script that shows one way of doing that

names default to here(1);

dt = Current Data Table();


// The formula that needs to be passed into the new column JSL needs
// to look like
//     Max( :NameA, :UniqueX, :Name bb )
// A literal string of ":NameA, :UniqueX, :Name bb" needs to be created
// and then plugged into the JSL and then executed
//  ":NameA, :UniqueX, :Name bb" is just an illustration, what the 
// make up of the string needs to be derived from the actual column
// names in the data table
FreqList = dt << get column names( string, numeric );
freqString = "";

// build the JSL in the form that is required for the formula
For( i = 1, i <= N Items( FreqList ), i++,
	If(
		Contains( FreqList[i], "Unique" ) == 1 |
		Contains( FreqList[i], "Name" ) == 1, 
		If( i==1, delim = "", delim = ",");
		freqString = freqString || delim || " :" || FreqList[i]
	)
);

// Run the command, passing into the formula the command string developed above
Eval( Parse( "dt << New Column (\!"Freq\!", numeric, Formula( Max( " || freqString || " )));" ) );

Re: How to create a formula column to find the maximum of several columns

GabeM27 — Mon, 30 Mar 2020 21:19:36 GMT

@txnelson You are a wizard! Thank you for the help.

Re: How to create a formula column to find the maximum of several columns

Devin — Tue, 26 Apr 2022 17:24:53 GMT

This may be non sequitur, but what column formula would result in not the <value> of the maximum column, but the <column name> of the column with the maximum value? TIA

Re: How to create a formula column to find the maximum of several columns

txnelson — Wed, 27 Apr 2022 05:12:50 GMT

here is a simple example displaying the column name of the max column

Names Default To Here( 1 );
// Open Data Table: semiconductor capability.jmp
// → Data Table( "semiconductor capability" )
dt = Open( "$SAMPLE_DATA/semiconductor capability.jmp" );

dt << New Column( "Example",
	character,
	formula(
		If( Row() == 1,
			namelist = {"NPN1", "NPN2", "NPN3", "NPN4"}
		);
		maxes = [];
		For( i = 1, i <= N Items( namelist ), i++,
			maxes = maxes || Matrix( Column( namelist[i] )[Row()] )
		);
		If( Row() <= 5,
			Show( maxes )
		);
		namelist[Loc( maxes, Max( maxes ) )[1]];
	)
);

Re: How to create a formula column to find the maximum of several columns

jetpeach — Thu, 26 Oct 2023 06:28:06 GMT

i'm not sure why, but txnelsons code didn't work for me.

after debugging, i had to change last line to:

namelist[ Loc( as list(maxes`), Max( maxes ) )[1] ];

now it seems working