Use list of values in formula

Question

Hi,

I´m trying to calculate the distance that animals have travelled at a given time point adjusted for baseline.

I have identified the total distance for each animal (&AM distance) and now need to subtract the baseline distance. This baseline is defined by 30 min after dosing. As the distance in the dataset is accumulated values I need to find the one value that matches the baseline timepoint. I have done this as shown below (which works and results in a list of baseline values):

Dist_baseline = :Name( "DistK-[cm]" )[Data Table( "TSE data" ) << Get rows Where(
	:("Time since dosing-[hours]") == 0.5
)]

The question is now, can I do the above (get rows where) with identifiers(animal no) and combine it with the table so that I can calculate the correct value for each animal?

TSE data test.jmp

6AM distance.jmp

JulieSAppel · Answer

Actually, for each animal I need to identify the accumulated distance travelled (column "DistK-[cm]") at the last test date at the time that is closest to 6 AM. These values are shown in the "Dist 6 AM" column (and summarized in the 6 AM distance data set (I´m not sure that I need this though)).

Then I need to adjust it for baseline, meaning, I need to subtract the accumulated distance travelled until 30 min after dosing.  For this I would use the "Time since dosing" column, identify the rows where the value in this column equals 0.5 hours (which is what I did with "get rows where" ) which provides me a list of values (one value per animal) [1218, 2999, 5368, 2187, 2242, 3457, 3171, 5008, 4022, 3822, 5429, 3060, 2972, 3162, 5659, 2280].

So in this example animal 1 has travelled 43343 cm at 5.58 AM and then I need to subtract the value at 30 min after dosing (in this case the first value in the list: 1218 cm). How do I do this in a robust manner? With a loop that iterates through the list?

martindemel · Answer

Not fully clear to me what you want to achieve ultimately.  
1. In the code above you select rows.You say you want to select rows based on identifiers like Animal Number, correct? 
2. This is done in the table TSE ....jmp. In 6AM you have the sum of the column "DistK-[cm]" by Animal No using a summary table. Is that's what you want to get calculated for the selected Animal Number? 
Thanks for clarification

wgardnerQS · Answer

I think adding the formula below to a column in your TSE data test datatable will get you what you want

Col Mean( If( :Name( "Time since dosing-[hours]" ) == 0.5, :Name( "DistK-[cm]" ) ), :Animal No. )