85027_en-US

liliconcom

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

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

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 =&nbsp;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