Choose Language Hide Translation Bar
Highlighted
JulieSAppel
Level IV

Row iteration and creating a new column

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Row iteration and creating a new column

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 )),
			.
		)
	);
Jim

View solution in original post

6 REPLIES 6
Highlighted
ron_horne
Super User

Re: Row iteration and creating a new column

Hi @JulieSAppel 

the final results are conflicting. since you are telling jump to put a number or a character in the same column. the program just can't take it. try putting a missing value (or zero) instead of "no value"

Highlighted
JulieSAppel
Level IV

Re: Row iteration and creating a new column

I actually never put that string in there, it was only to demonstrate that the cell should be empty if animal no. is not identical to the row below

Highlighted
txnelson
Super User

Re: Row iteration and creating a new column

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 )),
			.
		)
	);
Jim

View solution in original post

Highlighted
JulieSAppel
Level IV

Re: Row iteration and creating a new column

Thanks a lot. That makes sense (and works perfectly). 

I was looking into using a for each row iteration though. Why is that not an option here?

Highlighted
txnelson
Super User

Re: Row iteration and creating a new column

Formulas automatically loop through all rows in a data table, so if you were allowed to use a For Each Row to modify a formula, every time you changed the formula(i.e. each row in the For Each Row), the formula would run through all rows.
Jim
Highlighted

Re: Row iteration and creating a new column

Also, be careful how you use the Lag() function.

 

// previous row
Lag( :Animal No., 1 )

// next row
Lag( :Animal No., -1 )

A lag of +1 accesses the previous row. A -1 lag accesses the next row.

Learn it once, use it forever!
Article Labels

    There are no labels assigned to this post.