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

## 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.

 Group Sample Number Data Value Desired Formula Column A 1 22.1 200 A 2 104.2 200 A 3 200 200 B 1 18.6 199.5 B 2 95.4 199.5 B 3 105.8 199.5 B 4 170 199.5 B 5 199.5 199.5 C 1 40 100 C 2 60 100 C 3 80 100 C 4 100 100

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

Juliette

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

## 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 )
);``````
2 REPLIES 2
Highlighted

## 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 )
);``````
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.