243153_en-US

liliconcom

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.

 

Thanks in advance!

 

Juliette

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.

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 )
);