Choose Language Hide Translation Bar
Highlighted
Reinaldo
Level IV

How to copy and paste data cells from different ID cells?

Hi,

I have an example of data table enclosed in this post. I need to copy the information from ProTest cells from Phase B to Phase A in the same column. However, first I need to match the Test_IDs following the rule:

 

Phase A <-> Phase B

1                        11

3                        13

5                        15

7                        17

9                        19

 

I have different numbers of participants because of missing data.

How can I do it, please?

Many thanks!

~Rei
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
gzmorgan0
Super User

Re: How to copy and paste data cells from different ID cells?

Even though your table is sorted and nice,  this script assumes nothing, even allows the possibility that there is no corresponding ProTest.

Names Default to Here(1);
dt = Data Table("example2");

For(i=1, i<=nrow(dt), i++,
 if (:Phase[i] =="A",
 	uid = :Subject_ID[i];
 	tid = :Test_ID[i] +10;
    xx = dt << get rows where(:Subject_ID ==uid & :Test_ID == tid);
    if(nrow(xx)==1,
      :ProTest[i] = :ProTest[xx[1]]
    );
 ); //end if Phase
); // end For i

 

 

View solution in original post

Highlighted
gzmorgan0
Super User

Re: How to copy and paste data cells from different ID cells?

oops!  The script is always getting just the first value which is phase A.  So modify the statement with an additional filter & :Phase=="B"

    xx = dt << get rows where(:Subject_ID ==uid & :Test_ID == tid & :Phase=="B");

 

View solution in original post

7 REPLIES 7
Highlighted
gzmorgan0
Super User

Re: How to copy and paste data cells from different ID cells?

Even though your table is sorted and nice,  this script assumes nothing, even allows the possibility that there is no corresponding ProTest.

Names Default to Here(1);
dt = Data Table("example2");

For(i=1, i<=nrow(dt), i++,
 if (:Phase[i] =="A",
 	uid = :Subject_ID[i];
 	tid = :Test_ID[i] +10;
    xx = dt << get rows where(:Subject_ID ==uid & :Test_ID == tid);
    if(nrow(xx)==1,
      :ProTest[i] = :ProTest[xx[1]]
    );
 ); //end if Phase
); // end For i

 

 

View solution in original post

Highlighted
Reinaldo
Level IV

Re: How to copy and paste data cells from different ID cells?

Brilliant, @gzmorgan0! :)

I have two questions:

1. In case I would like to add more "filters" or "By", should I create more variables (e.g., uid, tid) and insert them into the function "get rows where ( )", adding & "Column name == 'new variable'"?

 

2. If the Test_id "cannot" be defined as a logical sequence such as "sum 10" please do you think it is possible to create, for example, before (or inside) "For looping statement" a matrix which associates each Test_id from Phase A to Phase B? In this way, the user could define any association between id's in both phases.

 

I would like to thank you for your help!

 

Best!

 

PS: It is enclosed in this post a suggestion for following association and I added your script into there:

 

Phase A        Phase B

1                       20

2                       22

5                       55

8                       18

9                       9

~Rei
Highlighted
gzmorgan0
Super User

Re: How to copy and paste data cells from different ID cells?

You should look into JMP's associative array. Think of an associative array as a keyed list. You define the key and the associated values. Keys can be strings, expressions, lists, almost anything but numeric decimal values, integers are okay.  See the modified script below. In other scripting languages, an associative array is called a dictionary. I use it often. 

 

The answer is yes to your other question, add more filter variables.

Good luck.   

Names Default to Here(1);
dt = Data Table("example2_modified");

decode_aa = Associative Array({1,2,5,8,9}, {20,22,55,18,9});

For(i=1, i<=nrow(dt), i++,
 if (:Phase[i] =="A",
 	uid = :Subject_ID[i];
 	tid = decode_aa[ :Test_ID[i] ];
    xx = dt << get rows where(:Subject_ID ==uid & :Test_ID == tid);
    if(nrow(xx)==1,
      :ProTest[i] = :ProTest[xx[1]]
    );
 ); //end if Phase
); // end For i
Highlighted
Reinaldo
Level IV

Re: How to copy and paste data cells from different ID cells?

Hi @gzmorgan0

 

Thank you for your solution! Just one score couldn't be copied from Phase B to Phase A. It's Test_id = 9 (equal ids). I didn't understand why.

Your script is attached.

Many thanks!

~Rei
Highlighted
gzmorgan0
Super User

Re: How to copy and paste data cells from different ID cells?

oops!  The script is always getting just the first value which is phase A.  So modify the statement with an additional filter & :Phase=="B"

    xx = dt << get rows where(:Subject_ID ==uid & :Test_ID == tid & :Phase=="B");

 

View solution in original post

Highlighted
Reinaldo
Level IV

Re: How to copy and paste data cells from different ID cells?

Amazing, @gzmorgan0!! Worked!!

Where can I learn more about those commands such as get rows where () and nrow( )  and others to have a full control from rows and columns? Does the nrow( ) accept an integer inside it in case I wish to limit JMP scrolls the vector?

I will try to adapt to my problem... _/\_

 

Many thanks!! 

 

~Rei
Highlighted
gzmorgan0
Super User

Re: How to copy and paste data cells from different ID cells?

oops!  The script is always getting just the first value which is phase A.  So modify the statement with an additional filter & :Phase=="B"

    xx = dt << get rows where(:Subject_ID ==uid & :Test_ID == tid & :Phase=="B");

 

Article Labels

    There are no labels assigned to this post.