Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
robhanssen
Level III

reference to a baseline value

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 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
ms
Super User ms
Super User

Re: reference to a baseline value

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

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

View solution in original post

Highlighted
txnelson
Super User

Re: reference to a baseline value

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]];

performance.GIF

 

Jim

View solution in original post

6 REPLIES 6
Highlighted
Kevin_Anderson
Level VI

Re: reference to a baseline value

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!

Highlighted
ms
Super User ms
Super User

Re: reference to a baseline value

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

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

View solution in original post

Highlighted
ih
ih
Level VII

Re: reference to a baseline value

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.
Highlighted
praco
Level II

Re: reference to a baseline value

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

Highlighted
txnelson
Super User

Re: reference to a baseline value

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]];

performance.GIF

 

Jim

View solution in original post

Highlighted
praco
Level II

Re: reference to a baseline value

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

Nico
Article Labels

    There are no labels assigned to this post.