Row iteration and creating a new column

JulieSAppel

Hi,

 

Im trying to create a new column where I calculate AUC. This value should only be calculated as long as animal no is identical.

So far I managed to create a new column and calculate the values:

 

 

(Data Table( "jmp forum row ite" ) << New Column( "AUC per animal",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		0.5 * (:Name( "VO2(1)-[ml/h/kg]" ) + Lag( :Name( "VO2(1)-[ml/h/kg]" ), -1 )) * (
		:Name( "Time since dosing-[hours]" ) - Lag( :Name( "Time since dosing-[hours]" ), 1 ))
	)
));

 

But when I try to include a row iteration checking for animal no. being identical jmp tells me that this shouldn´t be combined.

 

For Each Row( If( :Animal No. == Lag( :Animal No., -1 ), Formula(), "no value" ) );

 

Should this be done in several steps or is there a better way of doing that?

jmp forum row ite.jmp

jmp forum row ite script.jsl

txnelson

The issue you are having, is that you are attempting to modify the formula of a column that already has a formula, and what you really need to do, is to do all of that in the initial invocation of the formula.  If you modify your initial formula to the one below, I believe you will have what you want.

Data Table( "jmp forum row ite" ) << New Column( "AUC per animal",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		If( :Animal No. == Lag( :Animal No., -1 ),
			0.5 * (:Name( "VO2(1)-[ml/h/kg]" ) + Lag( :Name( "VO2(1)-[ml/h/kg]" ), -1 )) * (
			:Name( "Time since dosing-[hours]" ) - Lag( :Name( "Time since dosing-[hours]" ), 1 )),
			.
		)
	)
);

If you want to modify a formula for an already existing column, you have to swap out the entire formula.  See the example below:

Data Table( "jmp forum row ite" ) << New Column( "AUC per animal",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
			0.5 * (:Name( "VO2(1)-[ml/h/kg]" ) + Lag( :Name( "VO2(1)-[ml/h/kg]" ), -1 )) * (
			:Name( "Time since dosing-[hours]" ) - Lag( :Name( "Time since dosing-[hours]" ), 1 ))
		)
	)
;

wait(5); // pause for 5 seconds to allow for the viewing of the data table with the original formula

:name("AUC per animal") << Formula(
		If( :Animal No. == Lag( :Animal No., -1 ),
			0.5 * (:Name( "VO2(1)-[ml/h/kg]" ) + Lag( :Name( "VO2(1)-[ml/h/kg]" ), -1 )) * (
			:Name( "Time since dosing-[hours]" ) - Lag( :Name( "Time since dosing-[hours]" ), 1 )),
			.
		)
	);