Table scripting help: Syntax for new formula columns, with conditions for row and column valuesHave 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.
Re: Table scripting help: Syntax for new formula columns, with conditions for row and column valuesThank you, Thierry! That was an easy fix 
Re: Table scripting help: Syntax for new formula columns, with conditions for row and column valuesThe 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
Re: Table scripting help: Syntax for new formula columns, with conditions for row and column values@TRR21 perhaps you can accomplish what you are looking for by using the lag function. As well as previous post linked by @Mark_Bailey . You can search the lag function on the discussion board for relevant examples.
Re: Table scripting help: Syntax for new formula columns, with conditions for row and column valuesMy situation seems different than that in those examples.Re: Table scripting help: Syntax for new formula columns, with conditions for row and column valuesThis request has been asked before. Will this approach work for your situation?
