Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- How to repeat value from higher number row to lower number rows?

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jan 27, 2020 9:58 PM
(1002 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Created:
Jan 28, 2020 10:04 AM
| Last Modified: Jan 28, 2020 10:46 AM
(976 views)
| Posted in reply to message from ih 01-28-2020

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.