cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
  • JMP 19 is here! See the new features at jmp.com/new.
  • Due to global connectivity issues impacting AWS Services, users may experience unexpected errors while attempting to authorize JMP. Please try again later or contact support@jmp.com to be notified once all issues are resolved.

Discussions

Solve problems, and share tips and tricks with other JMP users.
Choose Language Hide Translation Bar
JMPUser9
Level II

Conditional Formula

I am trying to add a column formula that is conditional to other columns. I have data where “Results” Column is stacked by “Subject” and “Product” with multiple values over “Time”. I’m trying to calculate the baseline for each value in “Results” so that:

 

New Column = "Results" – ["Results" value where Time=(1) for that particular Subject/Product combination]

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Conditional Formula

Sometimes it's  easier to attack problems like this in several steps, rather than writing complex jsl for a column formula. Here's an approach that uses "Subset" to extract the Time=-5 rows, joins them back onto the original data table with "Update", and then performs a simple subtraction in a column formula. If you're running JMP 17, workflows are a great way to build and share this kind of thing.

 

View solution in original post

6 REPLIES 6
mmarchandTSI
Level V

Re: Conditional Formula

Can you give an example of the data?  That will help with finding a proper solution.

jthi
Super User

Re: Conditional Formula

Just a guess as there is no data to test this with

:Result - Col Min(If(:Time == 1, :Result, .), :Group);

 

Edit:

Updated formula after we received sample data

:Results - Col Min(If(:Time == -5, :Results, .), :Subject, :Product)

 

-Jarmo
txnelson
Super User

Re: Conditional Formula

If your data are sorted by Subject, Product, Time then this example will work

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );
dt:age << set name( "Subject" );
dt:sex << set name( "Product" );
dt:weight << set name( "Results" );

dt << New Column( "baseline",
	formula(
		If( Row() == 1,
			valueTime1 = :Results,
			If( :Subject != Lag( :subject ) | :Product != Lag( :product ),
				valueTime1 = :Results
			)
		);
		baseline = :Results - valueTime1;
	)
);
Jim
JMPUser9
Level II

Re: Conditional Formula

I've included sample data. I need to add a new column where the formula takes the Time(-5) Results value for each Subject/Product combination and subtracts that value from each Result in the Subject/Product Combination. 

Re: Conditional Formula

Sometimes it's  easier to attack problems like this in several steps, rather than writing complex jsl for a column formula. Here's an approach that uses "Subset" to extract the Time=-5 rows, joins them back onto the original data table with "Update", and then performs a simple subtraction in a column formula. If you're running JMP 17, workflows are a great way to build and share this kind of thing.

 

mmarchandTSI
Level V

Re: Conditional Formula

The JSL I came up with isn't too complicated.  I didn't write a formula but a table script.  You could run this whenever you add more data.

 

dt = Current Data Table();
OffsetArray = Associative Array();
TimeRows = dt << Get Rows Where( :Time == -5 );
For Each( {val, idx}, TimeRows, OffsetArray[:Subject[val] || :Product[val]] = :Results[val] );
For Each Row( dt, :New Column = :Results - OffsetArray[:Subject || :Product] );

edit

 

After looking at the table more, I noticed the missing values in some :Results cells.  You should recode those to 0 or alter the script a bit:

 

dt = Current Data Table();
OffsetArray = Associative Array();
TimeRows = dt << Get Rows Where( :Time == -5 );
For Each( {val, idx}, TimeRows, OffsetArray[:Subject[val] || :Product[val]] = If( !Is Missing( :Results[val] ), :Results[val], 0 ) );
For Each Row( dt, :Results with Offset = :Results - OffsetArray[:Subject || :Product] );

 

Recommended Articles