topic How to connect formulas to dedicated colums instead of row number in Discussions

How to connect formulas to dedicated colums instead of row number

mkunzi — Sat, 10 Jun 2023 23:30:22 GMT

Hey everybody,

From my predecessor I inherit some data files with these kind of formulas (see also attached file):

Match( :Phase,
	"Preparation", (:Date_time[2] - :Date_time[1]) / 3600,
	"Cultivation", (:Date_time[5] - :Date_time[3]) / 3600,
	"Harvest", (:Date_time[7] - :Date_time[6]) / 3600
)

I would like to exchange the row numbers with dedicated Timepoints, so that it doesn't matter at what row of the table the data is.

In my head it looks like: "Cultivation", (:Date_time[:Timepoint == "End"] - :Date_time[:Timepoint == "Inoculation"]) / 3600

Unfortunately this doesn't work and I don't find any suitable code. Can you help me with it?

Thx,

Markus

PS: as I am new to Scripting in general and JMP in special, please excuse me if I use the wrong language or ask stupid questions.

Re: How to connect formulas to dedicated colums instead of row number

jthi — Tue, 18 May 2021 09:08:23 GMT

To get the row numbers you need, you can use Loc() or Contains functions. Below is an example using Contains()

Match(:Phase,
	"Preparation", (:Date_time[2] - :Date_time[1]) / 3600,
	"Cultivation", (:Date_time[Contains(:Timepoint << get as matrix, "End")] - :Date_time[Contains(:Timepoint << get as matrix, "Inocultation")]) / 3600,
	"Harvest", (:Date_time[7] - :Date_time[6]) / 3600
)

Not exactly answer to your question, but in this case you could maybe use something like this (if you only need first and last times per Phase):

(Col Max(:Date_time, :Phase) - Col Min(:Date_time, :Phase)) / In Hours(1)

Re: How to connect formulas to dedicated colums instead of row number

mkunzi — Tue, 18 May 2021 13:43:20 GMT

Wow, that was quick.

Thak you @jthi for your help, it is exactly what I was looking for.

As it is not always Beginn and End I decided for the following code:

Match( :Phase,
	"Cultivation",(:Date_time[Loc( :Timepoint << get as matrix, "End" )] - :Date_time[Loc( :Timepoint << get as matrix, "Inocultation" )]) / In Hours( 1 )
)

I chose Loc, because I understand it as Location and now it is easy readable.