Column Formula, Relative Value Calculations

[#1]

I have been trying to script a new column formula for a data table, where the calculated value changes based on values of columns to the left.  Example of what I mean using the "Big Class" example file. Let's say there are 200 rows as opposed to 20, each name appearing 10 times in succession as though the heights and wieghts were recoreded once a year for ten years, additionally there would be another column in the data table with the years the data was collected.  All the names would have the same years (i.e. 2001, 2002, 2003... 2010).  The new colummn I would like to script a formula for would be the height of that year minus year 2003 let's say. Maybe that year they started taking their vitamines and you want to show how that affected their height growth.  The new column would then be populated with values such as -.5, 0, .25, .7... all of them relative values to the specified height at year 2003 on a name-by-name basis.

Since there would be 20 year 2003, what kind of if/while/summarize/by/conditional formula would be used so each Name's heights are compared to that of the same person and not one at the other end of the data table?

Thanks in advance!

[#2 · Accepted solution]

Try something like this:

dt = Current Data Table();
tname = :name;
tyear = Col Min( :Year, :name );
trow = (dt << get rows where( tname == :name & tyear == :Year ))[1];
-1 * :weight[trow] + :weight[Row()];

[#3]

For the thirds line with Col Min, those tables would technically be characters, however according to this help page http://www.jmp.com/support/help/Statistical_Functions_2.shtml  min can have either all numeric or all strings so that shouldn't be a probelm, correct?

Second, what if I dont want the data to be compared to the minimum value, but rather one I choose.  Would setting up a :Year == " x y z" suffice in this scenario?

[#4]

Correct on both assumptions......but rather than asking, I suggest that you start trying such ideas on your own.  One really doesn't learn by asking, but by trying....failing and then succeeding

[#5]

Indeed. I have been failing and succeeding with this the past couple days making progress slowly but surely. This hiccup is just a part of a script that I've been stuck on.

[#6]

If it helps explain it better, let's redraw the example.  With the Big Class sample, I want a new column that it will compare all the female F heights individually to KATIE's height, and all the male M heights to JOHN's height.  This new column would be called "height difference", and each row would have that person's height minus KATIE or JOHN's height depending on M/F.  So rows 2, 3, 4 with LOUISE, JANE, and JACLYN will have "height difference" values of 2, -4, and 5 respectivly.  (Negative number are acceptable).  However, when it comes to TIM, his height will be compared to JOHN's 65 not KATIE's 59.

Taking it one step further, what if I wanted this done for every name on the list besides the ones who's age = 14.  All the rows where age != 14 will still have their respective "height difference" compared to KATIE and JOHN.  For those of age 14 I would like the cell to be blank.  

Hopefully this clears things up, and gives an example to work on as opposed to just taking about it.  If it makes it more confusing then completely ignore it :)

[#7]

All you are doing, is adding conditional items to your formula.  You will need to apply IF statements to the formula to cover the conditions that you have specified.  There is nothing difficult to this.  The method I showed you still fits, you just need to add the other conditions.

[#8]

I got the formula up and running by adding some more conditions as you suggested.  It was working well, but after adding another column with a similar formula and lines of code, running the script gets a "Not Responding" error.  Running the debugger, I have boiled the problem down to the "trow = dt << get rows where...".  The debugger says more than 50% of the runtime is being spent on that. 

From my understanding JMP runs the script all at once, so inserting a Wait(0); or dt << Run Formulas; will give it a breather.  What is the best practice in using these? I have tried using these in various ways; just one before the formula to a few disperesed before and after, yet still getting the Not Responding problem.

There are about three thousand rows in the data table and two columns with the formula, so it is understandable for the program to take time to think.  I have tried running the script on a second computer with higher specs, but get the same response.  Are there other methods for doing the "trow = dt << get rows where..." that would allow the program to break it down easier?