Scripting a loop (double loop maybe)Hello,
I need some help to build a script for a customer (see attached file).
He wants to calculate the "relative time" column automatically in JMP (while here it is calculated off-line in Excel).
For each "Batch number", when "New Batch Trigger" = 1, the "Date/Time" of the corresponding row is used as reference (it is set at "0") and the "relative time" is calculated as [ Date/Time i - Date /Time ref ].
I'm not really "script fluent" yet, any helps would be appreciated.
Thank you.
Emmanuel
Re: Scripting a loop (double loop maybe)Hi Emmanuel,
The easiest way to do this is interactively, using column formulas.
- First, edit the column formula you already have for the new batch flag, to allow for the first row. We want the value to equal 1 here, as well. Then, we will have 1 for the first row, and any row meeting the "new batch" criteria. (See formula image immediately below.)
- add a column transform for cumulative sum of the "new batch" row. The effect of this is to number each batch, allowing grouping. (see 2nd formula screenshot, and data table screenshot to see the numbering)
- add a formula column to compute the difference between time, and the column minimum time, grouped by the row you added in step one above (see 3rd formula screenshot, and data table screenshot to see resetting to 0 relative time for each new group.)
If the customer insists on scripting this, here is a script, which fixes the formula in the "new batch trigger" column and adds the last 2 columns in the table above.
Names Default To Here( 1 );
dt = Current Data Table();
dt:New Batch Trigger << formula( If( Row() == 1 | Dif( :Temperature C in °C, 1 ) + Dif( :Temperature B in °C, 1 ) < -60, 1, 0 ) );
dt << New Column( "Batch Group", formula( Col Cumulative Sum( :New Batch Trigger ) ) );
dt << New Column( "Rel time within group", formula( :"Date/Time"n - Col Minimum( :"Date/Time"n, :batch group ) ) );
Table attached.
Cheers,
Brady
Re: Scripting a loop (double loop maybe)Hello Brady,
thank you. I'm still a bit confused as it does not correlates with their own "Relative time" column. I think the issue comes from the fact that their reference row is in the "middle" of the lot and thus there is negative "relative time".
Emmanuel
Re: Scripting a loop (double loop maybe)I ultimately used a column formula with subscript and row function (I was not aware of at the time I posted the request).
