reference to a baseline value

[#1]

We perform tests that measure performance of a device over time. The output is a value relative to the starting value. Right now to get to such relative performance, I setup a jmp sheet in such a way that I know what the reference is: Example:

Row     Device           Time               Performance
1             T1              0                     97
2             T2              0                     93
3             T3              0                     95
4             T1             100                    83
5             T2             100                    82
6             T3             100                    83

For the relative performance, I setup a formula script

perf_0 = match (Device, "T1", Performance[1], "T2", Performance[2], "T3", Performance[3]);
Performance / perf_0

This works well with a limited number of data points, but is prone to typing errors for large data sets. Is there a better way to have this automated and less prone to errors? Note that the Performance value at time 0, is not necessarily the maximum or minimum value.

[#2]

Hi, robhanssen!

As you've intuited, there are probably better ways to do what you intend.

I would recommend a script with an Associative Array.  Please reference the Scripting Guide, and the JMP mockup of your dataset and an example of a script enclosed.

Good luck!

[#3 · Accepted solution]

If the table is sorted after ascending time, this formula should work

:Performance / :Performance[Col Min(Row(), :Device)]

[#4]

Some ideas:

1. Make a new column indicating that the row is a baseline value, then search for it just as @ms suggested.
2. Use a separate data table and a linked column to find the baseline.  I have not found a reliably way to script this yet but if/once that is possible it you could cross out #3.
3. Use a separate data table and look the value up in that table (similar to vlookup).  A sample is attached, maybe someone knows of an easier formula though.
4. Use a table variable. In the same sample script the device value pairs are on individual lines and parsed with regex.

[#5]

Hi all, I have the same question. However, I need a more reliable solution, I mean, not relying on row number, as my sheet can be mixed up.

So, is there a way to get the value from :performance of the row having the lowest value in :Time for each device, or even better: the value corresponding at Time=0 for each Device...

Maybe I'm not that clear...

Thank you

Nico

[#6 · Accepted solution]

Here is a formula that will work.  I have also attached a data table with it applied.

dt = Current Data Table();
currentDevice = :Device;
:Performance / :Performance[(dt << get rows where( :Device == currentDevice & :Time == 0 ))[1]];

[#7]

Very nice! Thank's
Just to understand, what is the function of [1] in the formula?

Nico