Choose Language Hide Translation Bar

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

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

3

3

3

Any help is appreciated!

Thanks,

Drew

1 ACCEPTED SOLUTION

Accepted Solutions

## Re: Finding/Copying a value into a cell on a different line

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 #")[]] ) );``````
2 REPLIES 2

## Re: Finding/Copying a value into a cell on a different line

Hi @drmn43,

try the following script on the attached table.

Otherwise it is much easier to do it manually following the same steps.

``````dt = Current Data Table();

dt << select where( :Phase == "Base" );
subdt = dt << Subset( Selected Rows( 1 ) );
dt << clear select;

// make a sleek change of names
subdt << delete columns( "Base by Patient" );
subdt:Name( "Yy = Variable of interest" ) << Set Name( "Base by Patient" );

// Before updating the it is useful to delete columns that we do not want to updat
keep = {"Patient #", "Base by Patient"};
todelete = subdt << get column names( "string" );
For( icol = 1, icol <= N Items( keep ), icol++,
Remove From( todelete, Contains( todelete, Keep[icol] ), 1 )
);
subdt << delete columns( todelete );

dt << Update( With( Data Table( "Subset of Database" ) ), Match Columns( :Patient # = :Patient # ) );

Close( subdt, no save );
``````

if you need the other rows empty i can add another step or two to get it done.

Ron

## Re: Finding/Copying a value into a cell on a different line

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 #")[]] ) );``````