230739_en-US

liliconcom

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?

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"

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

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

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?

Re: Row iteration and creating a new columnThanks a lot. That makes sense (and works perfectly). 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.