How to iteratively pull and store values from one column based on shared values in another column

[#1]

Hello,

I am new to JMP scripting and have been trying to write a script for a specific process given a data table to work with. To help explain what I want, here is a crude sample table.

What I want to do is to write a script that would iteratively check each row in the "Step Name" column for the value "Sleep". When it gets to a row with "Sleep", it will then check the corresponding value under the column "Speed". Then, I want the script to check all of the previous rows that share the same values under the "Speed" column. While it shares the same value under the "Speed" column, it will then check for existing values under the "Material" column. If a value exists under "Material", it will take that value and the corresponding value under "Speed" and output them in a new table, like this:

I have an idea that this would require an extensive For loop, but I don't know how to approach it. I would greatly appreciate any suggestions on how to write this script. Thank you

[#2]

Just based on your example data would it be enough to just remove all rows with missing material and set all values of step name to sleep?

[#3]

I believe so yes, that should be enough to just remove the rows with missing material while setting the remaining values of step name to sleep. How would I approach writing a script for that?

[#4 · Accepted solution]

This can be also done interactively from JMP but it is faster for me to just write it as a script so here it is. Notice that have a bit different table than you do (different column names and values).

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(10),
	Compress File When Saved(1),
	New Column("Step", Character, "Nominal", Set Values({"W", "E", "P", "F", "S", "C", "W", "P", "S", "F"})),
	New Column("S", Character, "Nominal", Set Values({"F", "F", "F", "F", "F", "S", "S", "S", "S", "S"})),
	New Column("M", Character, "Nominal", Set Values({"", "F", "", "P", "", "", "S", "G", "", ""}))
);

wait(1); // demo purposes
dt << Select Where(Is Missing(:M)) << Delete Rows; // << Get Rows Where() / << Where() are generally faster than select where
wait(1); // demo purposes
Column(dt, "Step") << Set Each Value("S");

[#5]

I modified this script to fit my use in my data table and it looks good, thank you so much!