How to apply mathematical operations to selected rows only?

Bluegalaxy — Sat, 10 Jun 2023 23:53:14 GMT

Hello,

I am new to JMP and struggling to calculate % change in current for 5 different devices after 72hrs of stress testing as shown in the table below. The % change in current is calculated as = (current @ a given stress duration - current @ 0hr)/current @0hr*100

This operation is quite straightforward in Excel but not sure how to apply the formula to selective rows? Any help will be greatly appreciated! I am using JMP15.0 version.

Re: How to apply mathematical operations to selected rows only?

txnelson — Wed, 17 Aug 2022 02:01:46 GMT

Below are 3 methods for doing what you want.

Names Default To Here( 1 );
dt = Data Table( "Bluegalaxy" );

// Method 1
dt << New Column( "% Change 1",
	Format( "Percent", 9, 2 ),
	set each value(
		currDevice = :Device Number;
		zeroCurrent = :Current[((dt << get rows where(
			:Device Number == currDevice & :Stress Duration == 0
		))[1])];
		value = (:Current - zeroCurrent) / zeroCurrent;
	)
);

// Method 2
// Sort the table in assending Device Number/Stress Duration
dt << sort(
	By( :Device Number, Stress Duration ),
	order( Ascending, Assending ),
	Replace Table( 0 )
);
dt << New Column( "% Change 2",
	Format( "Percent", 9, 2 ),
	set each value(
		If( :Stress Duration == 0,
			zeroCurrent = :Current
		);
		(:Current - zeroCurrent) / zeroCurrent;
	)
);
// Sort the table back to the original order
dt << sort(
	By( :Stress Duration, Device Number ),
	order( Ascending, Assending ),
	Replace Table( 0 )
);

// Method 3
// Create a data table with only the 0 Stress Duration values
dt << Select Where( :Stress Duration == 0 );
dtZero = dt << subset( selected rows( 1 ), columns( :device Number, :current ) );
dtZero:current << set name( "zeroCurrent" );
dt << Update( With( dtZero ), Match Columns( :Device Number = :Device Number ) );
dt << New Column( "% Change 3",
	Format( "Percent", 9, 2 ),
	set each value( (:Current - :zeroCurrent) / :zeroCurrent )
);
// Clean up 
dt << delete columns( zeroCurrent );
Close( dtZero, nosave );
dt << clear select;

All of these methods can be created in an interactive mode.

Note: I am using "Set Each Value" in the specification of the new columns. This can be swapped out for "Formula". The Formula specification allows for any new records to have the formula applied when they are added, or changed. In the case you have presented, I am assuming that you will want the values created only once, when the new column is created.

Re: How to apply mathematical operations to selected rows only?

Bluegalaxy — Wed, 17 Aug 2022 02:54:19 GMT

Thanks a lot Jim, for showing multiple ways to solve this problem! Your help is greatly appreciated!

topic Re: How to apply mathematical operations to selected rows only? in Discussions

How to apply mathematical operations to selected rows only?

Re: How to apply mathematical operations to selected rows only?

Re: How to apply mathematical operations to selected rows only?