topic Re: Conditional Formula in Discussions

Conditional Formula

JMPUser9 — Wed, 20 Dec 2023 15:27:05 GMT

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

Re: Conditional Formula

mmarchandTSI — Wed, 20 Dec 2023 15:43:35 GMT

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

Re: Conditional Formula

jthi — Thu, 21 Dec 2023 05:58:52 GMT

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)

Re: Conditional Formula

txnelson — Wed, 20 Dec 2023 16:11:57 GMT

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

Re: Conditional Formula

JMPUser9 — Wed, 20 Dec 2023 20:09:22 GMT

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

Jordan_Hiller — Wed, 20 Dec 2023 21:14:58 GMT

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.

Re: Conditional Formula

mmarchandTSI — Wed, 20 Dec 2023 22:14:23 GMT

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