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

- JMP User Community
- :
- Discussions
- :
- Column value changing with sample number and a fixed test condition only

- 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

Column value changing with sample number and a fixed test condition only

Apr 4, 2020 12:29 PM
(889 views)

I searched and couldn't find another question like this. I'm guessing there is a simple solution. I'd like to realize the solution in a column formula if possible.

Please see attached data set.

I have a sample (1st column) on which I measure a critical parameter (3rd column) at varying test conditions (2nd column).

I want to create a new column ("desired column") whose value is set by a formula to be the critical parameter measurement at only one specific test condition (and so is the same for all other rows) but changes by sample. The test condition I'm selecting in the example is 15.

The purpose of "desired column" is to be able to mark/filter certain samples (and their associated other rows at other test conditions) as good or bad or distinguish them from others based on this critical parameter value at the specific test condition.

In the data set, I show I understand I can use a 'conditional if' to set a column ("Crit Param at TC=15") that has a row value of the critical parameter at the target test condition but how do I propagate this value to all other rows for the given sample?

Many thanks in advance.

4 REPLIES 4

Highlighted
##

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

Re: Column value changing with sample number and a fixed test condition only

`:Crit Param[Row() + (15 - :Test Condition) / 5]`

My solution depends on test condition always having the same format so that my calculation of how far I am from the Test Condition = 15 row works. Curious to see if others have a more general solution. The subscript on the column name points to the row to grab the value from.

Highlighted
##

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

Re: Column value changing with sample number and a fixed test condition only

As almost always true, there are multiple ways to solve the problem. @KarenC provided a good one, and here is one that does not require the data to be in any order

```
dt = Current Data Table();
curSample = :Sample( Row() );
Max( :Name( "Crit Param at TC=15" )[dt << get rows where( :Sample == curSample )] );
```

Jim

Highlighted
##

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

Re: Column value changing with sample number and a fixed test condition only

Thanks to you both for prompt responses! Both getting at what I needed. And I learned that script like this can be used in column formulae.

Since txnelson's response was more general, I applied that. The actual data set has 500k rows and it's taking some horsepower to process. I'm guessing that maybe KarenC's solution might be less computationally intensive.

Highlighted
##

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

Re: Column value changing with sample number and a fixed test condition only

You are correct, that @KarenC code will run faster. Here is another way to do what you want, that will run fast. It is a JSL Script, so you just need to run it, and it will create the new column. It sorts the data so that your Critical Parameter at TC=15 is the top row for each sample, then it just applies that value until it sees the change in the data, etc. It then resorts the data back to the original, and cleans up on itself

```
Names Default To Here( 1 );
dt = Data Table( "Column Value by 2nd Column Question" );
dt << New Column( "Row Number", formula( Row() ) );
dt:Row Number << delete formula;
dt << Sort( By( :Sample, :Name( "Crit Param at TC=15" ) ), Order( Ascending, Descending ), replace table( 1 ) );
dt << New Column( "Desired \!"Crit Param at TC=15\!"",
formula(
If( Row() == 1, hold = . );
If( Is Missing( :Name( "Crit Param at TC=15" ) ) == 0,
hold = :Name( "Crit Param at TC=15" )
);
hold;
)
);
dt:Name("Desired \!"Crit Param at TC=15\!"") << delete formula;
dt << Sort( By( :Row Number), Order( Ascending), replace table( 1 ) );
dt << delete columns(Row Number);
```

It might be faster than @KarenC version, given the size of your data table

Jim

Article Labels

There are no labels assigned to this post.