Finding/Copying a value into a cell on a different line

Question

I am having trouble finding a good solution to my particular problem even after some extensive searching in the forum and in the JMP help files.

I have a dataset with patients, each of whom has multiple phases of a study in successive rows (because each patient has a different number of phases).  One of the columns is the phase and in one of these rows for each patient, there is a phase referred to as "Base."  I am trying to define a new variable to place the Yy value of "Base" in same row as the patient identifiers.  See below:

Patient #   Patient Name   Phase   Yy = Variable of interest  New Column = "Base" [by patient]

1                Patient XYZ     abc                    4                                              // want 2 here

1                                        def                     8

1                                        Base                  2

1                                        ghi                     10

2                Patient MNO   abc                    45                                           // want 54 here

2                                        def                     36

2                                        ghi                     24

2                                        Base                 54

3

Any help is appreciated!

Thanks,

Drew

gzmorgan0 · Accepted Answer

There are multiple methods to do this. @ron_horne's method can be done with point and click. The script below uses an Associative Array which is called a Dictionary or Lookup in other software.

I missed @ron_horne's table, so I have a different one. Here is the script and attached is a table with this script enbedded as a table script. Just click to run.

Names default to here(1);

dt    = current data table();
brows = dt << get rows where(:Phase == "Base");
aa = Associative Array( Column("Patient #")[brows], Column("Yy = Variable of interest")[brows]);
// patient numbers are the keys and the Base values, the values

nc = dt << New Column("New Column = \!"Base\!" [by patient]", numeric, Continuous);
nc << set each value(If(length(:Patient Name) > 0, aa[Column("Patient #")[]] ) );