BookmarkSubscribe
Choose Language Hide Translation Bar
TRR21
Community Trekker

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

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

IDVisitDateValue

1001

BLddMONyyyy1y1
1001wk6ddMONyyyy2y2
1001wk24ddMONyyyy3

y3

1002BLddMONyyyy4y4
1002wk16ddMONyyyy5y5
1003BLddMONyyyy6y6

1003

wk 6ddMONyyyy7y7
1003wk 24ddMONyyyy8y8

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.

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
Thierry_S
Community Trekker

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

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

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!
0 Kudos
TRR21
Community Trekker

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.
0 Kudos
GM
GM
Community Trekker

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 @markbailey .  You can search the lag function on the discussion board for relevant examples.

Thierry_S
Community Trekker

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

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
TRR21
Community Trekker

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

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