Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Row iteration and creating a new column

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Oct 25, 2019 4:20 AM
| Last Modified: Oct 25, 2019 4:38 AM
(1168 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

6 REPLIES 6

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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
##

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Row iteration and creating a new column

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.