Subscribe Bookmark RSS Feed

JSL: Alter values in a range of rows based on matching strings in a 2nd column?

cwbrett

New Contributor

Joined:

Apr 10, 2017

Our data collection software starts slightly earlier than our experiments do, so we'd like to eliminate that lag time in the JMP data in the following way:

 

1. User inputs date-time of start of each experiment ( can be multiples that start at different times in the same data table). This part I've got scripted with a List Box.

2. Parse date-time.

3.  Find range of rows correlated to each specific experiment based on matching the string identifier in Column A

4.  Remove all rows prior to starting date-time.

5.  Change the elapsed experiment time (in decimal time) to start at the input date-time.

 

I've been succesful at everything up to #5, where I keep getting error messages. Ideally, I'd just subtract the number in the now first row of the range for each experiment from each cell in the elapsed time column. Would a loop make the most sense here? Should I just create a new column for each experiments elapsed time and then recombine? Thanks for any help and scripts.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ian_jmp

Staff

Joined:

Jun 23, 2011

Solution

You might find something of interest in this thread, though the details will depend on the specific format of your data.

2 REPLIES
ian_jmp

Staff

Joined:

Jun 23, 2011

Solution

You might find something of interest in this thread, though the details will depend on the specific format of your data.

cwbrett

New Contributor

Joined:

Apr 10, 2017

Thanks, this was very helpful, I've managed to make this work in my script but have come up to another roadblock. The data points dont perfectly line up with my start time, so I end up with my first time point being off by up to a minute. I'd like to pull that first time point to 0 instead of the ~0.01 its at now. However, I'm not getting any values in my adjusted time column. The following is my code where the timestart is suuplied. 

t1 = dt << Get Rows Where (:BatchID == "ABCD");
t2 = dt << Get Rows Where (:BatchID == "EFGH");

dt << New Column("TimeStart", Number);
:TimeStart[t1] = timestart1;
:TimeStart[t2] = timestart2;

dt << New Column("NewEFT", Number, Formula((:Datetime-:TimeStart)/3600));
dt << Select Where(:NewEFT< 0);
dt << delete rows;

dt << Delete Columns("EFT_hrs");
dt << New Column("Adjusted EFT", Number);
:Adjusted EFT[t1] = :NewEFT - min(:NewEFT[t1]);
:Adjusted EFT[t2] = :NewEFT - min(:NewEFT[t2]);