Column Formula using data from other rowsHi! I have a database style data table that stores data from a bunch of process steps. Each row represents a sample from a given process step, and in that row, there is a reference to the parent sample. The parent sample is from the same material, but at the previous process step. I am mostly interacting with this database table through a jmpapp, so scripting is an acceptable way to manipulate the data.
Within this table, I want to calculate some parameters about each sample, including how that sample has changed compared to the parent sample. Some of those parameters might be calculated as:
measured data from child * measured data from parent.
Other parameters might be calculated as follows (where calculated value from parent would be similar to the equation above):
measured data from child * calculated value from parent
I have a currently working solution for this, but it is very slow, and requires a lot of calls to "Rerun Formulas".
So, before I describe my currently solution, I would like to get some recommendations for the best ways to accomplish this. Maybe I am going about the data structure the wrong way! If possible, it would be amazing if I could keep the calculations within this table.
Below is an image showing the current structure of the data and how it needs to be cascaded from parent to child in order to do all of the proper math.
Thanks for your help!
Re: Column Formula using data from other rowsThe column summary statistics in the Formula Editor can take one or more grouping variables.
Col Mean( :Parent Data, :ParentID )
Will give you the mean for the data in Parent Data grouped by ParentID.
Re: Column Formula using data from other rowsSee this link using a where statement with row() and subscripts
JMP Index Match
But it gets complicated the more you try to use it... I find for things like this its faster/easier to just use excel and write an import data script.
Re: Column Formula using data from other rows@Peter_Hersh, yes that makes sense, and is how I am currently doing this. It works OK, but because of the way I have the data grouped, I am currently looping through different subsets of the data, which takes quite a lot of time. Also, it would be nice if the parent data columns would update anytime the parent rows are updated, which is why I was hoping a column formula could be used. If I have to stick with the subset & update solution, I could use a "Proliferate parent data" script in the table or something.
@DonMcCormack , The Parent/Child relationships are being defined just by using the "ParentID" column. Each row includes the Sample ID as well as the Parent ID, so essentially, children have to look up the chain to the parents to find the data they need.
I found that it is possible to use a column formula kind of like this to get the parent data, but these formulas need to be run multiple times so that data can cascade down through the dependent rows/columns. In the example in the first table, the first time the formula runs, data from :Data will populated into :ParentData, but the :ParentData/ParentData column will be left blank because it's formula ran before the data was populated. Or.. .something like that at least.
Thanks for your thoughts!
dt = CurrentDataTable();
parent = :ParentID;
rows = dt << GetRowsWhere(:SerialNumber == parent);
Mean(Column(dt, "LayersMPAFraction")[rows]);
Re: Column Formula using data from other rowsHow are you defining the Parent/Child relationships? It would be hard to come up with a robust solution without knowing this first. A look at your original solution might help.
Re: Column Formula using data from other rowsOK, the Trick I am thinking would work well is to subset the original data with just the parent ID and Parent Data Column. Then join it back to the original data. Matching the Parent ID to the Child ID. Hope that is clear. Let me know if that is not clear
Re: Column Formula using data from other rowsThanks @Peter_Hersh !
Given my description, yeah Lag would work OK, but you're right in that the data is not always structured this way. The Child and Parent Data may be contained in different numbers of rows, at various distances from the new cells. For example, I might have 3 rows of data for Sample ID A, that I would want to average to include in all rows of the child. And there may be multiple children for the same parent.
Seems like I oversimplified my example haha. Maybe the other way you alluded to could help?
Re: Column Formula using data from other rows@mringqui, Yes making this should be no problem using the Lag function inside the formula builder. I am not sure if your data structure is always this way. There is a way to do this differently if the spacing between Parents and Children is not always the same.
Re: Column Formula using data from other rowsAnyone have any ideas?
