Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
user8421
Level I

How to repeat value from higher number row to lower number rows?

Here is an example data table with the 4th column "Desired Formula Column" showing the outcome I am trying to get.

 

GroupSample NumberData ValueDesired Formula Column

A

122.1200
A2104.2200
A3200200
B118.6199.5
B295.4199.5
B3105.8199.5
B4170199.5
B5199.5199.5
C140100
C260100
C380100
C4100100

 

I am looking for a column formula that will enter the value for the data value for the last timepoint of each group.

 

Thanks in advance!

 

Juliette

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
ih
ih
Level VII

Re: How to repeat value from higher number row to lower number rows?

If the Data always increases with the sample this is pretty straight forward using the Col Max function.  If the highest sample does not always have the highest data value, it is a little more complex. See one possible approach below, note that this will be slow for large tables:

 

New Column( "If Data always Increases with Sample Number",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( Col Max( :Data Value, :Group ) ),
	Set Display Width( 171 )
);

New Column( "If Data not related to Sample Number",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		MaxSample = Col Max( :Sample Number, :Group );
		g = Associative Array( Loc( :Group << Get Values(), :Group ) );
		s = Associative Array( Loc( :Sample Number << Get Values(), MaxSample ) );
		union = g;
		union << intersect( s );
		r = Max( union << get keys );
		:Data Value[r];
	),
	Set Display Width( 136 )
);

View solution in original post

2 REPLIES 2
Highlighted
ih
ih
Level VII

Re: How to repeat value from higher number row to lower number rows?

If the Data always increases with the sample this is pretty straight forward using the Col Max function.  If the highest sample does not always have the highest data value, it is a little more complex. See one possible approach below, note that this will be slow for large tables:

 

New Column( "If Data always Increases with Sample Number",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( Col Max( :Data Value, :Group ) ),
	Set Display Width( 171 )
);

New Column( "If Data not related to Sample Number",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		MaxSample = Col Max( :Sample Number, :Group );
		g = Associative Array( Loc( :Group << Get Values(), :Group ) );
		s = Associative Array( Loc( :Sample Number << Get Values(), MaxSample ) );
		union = g;
		union << intersect( s );
		r = Max( union << get keys );
		:Data Value[r];
	),
	Set Display Width( 136 )
);

View solution in original post

Highlighted

Re: How to repeat value from higher number row to lower number rows?

Sorry. I am deleting my comment since I had misread the problem. The accepted solution is definitely the way to go.

Dan Obermiller
Article Labels

    There are no labels assigned to this post.