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
- :
- Table scripting help: Syntax for new formula columns, with conditions for row an...

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
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 1, 2019 8:53 AM
(1055 views)

Have a table with several hundred rows with the following structure:

ID | Visit | Date | Value |

1001 | BL | ddMONyyyy1 | y1 |

1001 | wk6 | ddMONyyyy2 | y2 |

1001 | wk24 | ddMONyyyy3 | y3 |

1002 | BL | ddMONyyyy4 | y4 |

1002 | wk16 | ddMONyyyy5 | y5 |

1003 | BL | ddMONyyyy6 | y6 |

1003 | wk 6 | ddMONyyyy7 | y7 |

1003 | wk 24 | ddMONyyyy8 | y8 |

Note that the number of visits, what they are called can vary for subjects. But they all have a BL.

__Needs:__

1. For each given subject "ID", I need a column, say "Value delta" that computes difference between their "Value" corresponding to "BL" visit to any other visit. Expecting a 0 for rows with BL and postitive or negative value for other visit rows for each subject.

2. Like above, I need a columan, say "Time delta" that populates with the difference in no. of days between BL and other visit dates for each subject. BL rows will be expected to say "0" days and others with postitive or negative integers.

3. Need a third column that calculates a "Slope" based on all the "Value delta" and "Time delta" for each subject. Some subjects may not be computable if there is only one visit.

Any help will be greatly appreciated!

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions

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

The trick I use is to create columns for the Baseline data and dates on a table sorted by IDs AND Visit

Example for the Value (New column name = "Baseline Value")

```
If( :Visit == "BL",
:Value,
Lag( :BASELINE Value, 1 )
)
```

You then only need to a create column with the difference between Value and Baseline columns

Note: for the date, there is a special function to calculate difference:

`Date Difference( :DATESTART, :DATEEND, "day" )`

Hope that works for you.

If not, please attach a table with real or mock data where I could add the appropriate columns for you.

Best regards,

TS

Thierry R. Sornasse

5 REPLIES 5

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

Re: Table scripting help: Syntax for new formula columns, with conditions for row and column values

This request has been asked before. Will this approach work for your situation?

Learn it once, use it forever!

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

Re: Table scripting help: Syntax for new formula columns, with conditions for row and column values

My situation seems different than that in those examples.

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

Re: Table scripting help: Syntax for new formula columns, with conditions for row and column values

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

The trick I use is to create columns for the Baseline data and dates on a table sorted by IDs AND Visit

Example for the Value (New column name = "Baseline Value")

```
If( :Visit == "BL",
:Value,
Lag( :BASELINE Value, 1 )
)
```

You then only need to a create column with the difference between Value and Baseline columns

Note: for the date, there is a special function to calculate difference:

`Date Difference( :DATESTART, :DATEEND, "day" )`

Hope that works for you.

If not, please attach a table with real or mock data where I could add the appropriate columns for you.

Best regards,

TS

Thierry R. Sornasse

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

Re: Table scripting help: Syntax for new formula columns, with conditions for row and column values

Thank you, Thierry! That was an easy fix :)