Turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- How can i subtract row value from previous value and continue til end of table? ...

Topic Options

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

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

Jan 25, 2019 2:19 PM
(600 views)

Here is the sample. Delta column is what I am trying to acheive. Part# are category.

Part# | Data | Delta |

1 | 4 | 4 - 4 |

2 | 5 | 5 - 5 |

3 | 6 | 6 - 6 |

1 | 7 | 4 - 7 |

2 | 8 | 5 - 8 |

3 | 9 | 6 - 9 |

1 | 10 | 7 - 10 |

2 | 11 | 8 - 11 |

3 | 12 | 9 - 12 |

1 ACCEPTED SOLUTION

Accepted Solutions

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

Do I need to define "Delta Pattern" column?

6 REPLIES 6

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

Re: How can i subtract row value from previous value and continue til end of table? rows have mx val

As a column formula

`If( Row() <= 3, :Data, :Data - Row() );`

In a Script

```
For Each Row(
If( Row() <= 3, :Delta = :Data, :Delta = :Data - Row() );
);
```

Jim

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

Re: How can i subtract row value from previous value and continue til end of table? rows have mx val

Hi Jim,

thanks for prompt response.

this works in case the row number lines up with values in Data column for subtraction. what if there are random values in Data column and want to consider the raw values of data columns for subtraction?

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

Re: How can i subtract row value from previous value and continue til end of table? rows have mx val

Sample data

Part# | Data | Delta |

1 | 0 | 0 - 0 |

2 | 0 | 0 - 0 |

3 | 0 | 0 - 0 |

1 | 9 | 0 - 9 |

2 | 23 | 0 - 23 |

3 | 45 | 0 - 45 |

1 | 1 | 9 - 1 |

2 | 34 | 23 - 34 |

3 | 56 | 45 - 56 |

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

Re: How can i subtract row value from previous value and continue til end of table? rows have mx val

Here is how I would do it

```
Names Default To Here( 1 );
// Create the sample data table
dt = New Table( "Example",
Add Rows( 9 ),
New Column( "Part#", Character, "Nominal", Set Values( {"1", "2", "3", "1", "2", "3", "1", "2", "3"} ) ),
New Column( "Data",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [0, 0, 0, 9, 23, 45, 1, 34, 56] )
),
New Column( "Delta Pattern",
Character,
"Nominal",
Set Values( {"0 - 0", "0 - 0", "0 - 0", "0 - 9", "0 - 23", "0 - 45", "9 - 1", "23 - 34", "45 - 56"} )
)
);
// Add a new column to provide a path back to the original order
dt << New Column( "RowNum", formula( Row() ) );
// Delete the formula, so the values become static rather than calculated
dt:RowNum << delete formula;
// Sort the data by Part# and Row
dt << sort( by( Part#, :RowNum ), replace table( 1 ) );
// Create the new column with the delta values, which is pretty simple with all of
// the data sorted by Part#
dt << New Column( "Delta", formula( If( Lag( :Part# ) != Part#, 0, Lag( :Data ) - :Data ) ) );
// Delete formula so the values become static
dt:Delta << delete formula;
// Sort back into the original order
dt << sort( by( :RowNum ), replace table( 1 ) );
// Delete the Rownum column since it is no longer needed
dt << delete columns( "RowNum" );
```

There are otherways to do it too, but they would be slower to calculate

Jim

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

Do I need to define "Delta Pattern" column?

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

Re: How can i subtract row value from previous value and continue til end of table? rows have mx val

- In my example, the column that you called "Delta", I change to be called "Delta Pattern". I did that because I wanted the the results of the calculation to be placed into a column named "Delta", since the word delta refers to the difference. So, what you need to do, to match what I did, is to change the name of the column in your starting data table from "Delta" to "Delta Pattern".
- You have marked your latest response as a solution. This is not a solution, but another question. You need to remove the Solution selection from that item, and place on the item that actually shows the solution.
- The assumption that I believe most of the individuals that respond to questions posted in the Community Discussion, is that what is presented back to the questioner, is examined and understood. Not just copied and pasted into their solution without understanding what was provided for them. And if in the examination of the response, something is not understood, then a question on that specific area is asked back to the community. Therefore, the end result is learning, and movement by the questioner, to a point where they can start to contribute as a respondent to other community members.

Jim