topic Re: Replace multiple rows value from one column is not working properly in Discussions

Replace multiple rows value from one column is not working properly

nthai — Fri, 09 Jun 2023 19:48:31 GMT

Hi JMP community,

I have occured one issue that quite weird. Here's my code:

dt_temp = data table("aaaa");

dt_temp << Summary( Group( :Column1,:Column2), Freq( "None" ), Weight( "None" ),output table name( "summary" ) );
data table("summary") << select where (:N rows >= 2 & !contains(:Column1,"AA") & !contains(:Column1,"BB") );
rows = dt_temp << get selected rows; 

dt_temp:Column3[rows] = "";
//Column(dt_temp, "Column3")[rows] = "";

When i run this code, even the rows has value like [10, 11, 20, 21] but the end result from column3 is different. It replace others row (let's say row 1, 5, ...) instead of rows in get selected rows.

Could you help me to resolve this issue?

Re: Replace multiple rows value from one column is not working properly

jthi — Fri, 04 Jun 2021 04:45:59 GMT

Are you trying to replace values in the original data table or in the summary table? This seems to work just fine for me:

Names Default To Here(1);
dt_temp = Open("$SAMPLE_DATA/Big Class.jmp");
dt_temp << Summary(Group(:age, :sex), Freq("None"), Weight("None"), output table name( "summary" ));

data table("summary") << Select Where(:N Rows >= 3 & !Contains(:sex, "M"));
rows = dt_temp << Get Selected Rows;
show(dt_temp:name[rows]);
dt_temp:name[rows] = "";
show(dt_temp:name[rows]);

Re: Replace multiple rows value from one column is not working properly

nthai — Sat, 05 Jun 2021 14:43:35 GMT

Hi Jarmo,

Thank you for your suggestion but it seems doesn't work for me.

To clarify, i would like to replace values in original table based on the conditional of summary table.

I have tried to run your code as well, and here's the result:

Selected rows from summary table:

Selected rows from main table. There was 15 rows were selected but it replaced only 1st rows of each group:

Re: Replace multiple rows value from one column is not working properly

jthi — Sat, 05 Jun 2021 15:04:10 GMT

Which version of JMP are you using? I'm using 15.2.1 and my example seems to work just fine for me:

Re: Replace multiple rows value from one column is not working properly

nthai — Sat, 05 Jun 2021 16:15:08 GMT

i'm using Jmp Pro 12.2.0, 64 bit version. Log file for your reference:

Although log file showing row number (-1) is not invalid but when i shows rows, there's no such row like that.

Re: Replace multiple rows value from one column is not working properly

jthi — Sat, 05 Jun 2021 16:23:40 GMT

Based on this Data table subscripting the way I'm setting the cell values might have become available in JMP13.

I'll try to avoid looping but I don't have access to older version of JMP than 15, so I'm not sure if this will work either:

Names Default To Here(1);
dt_temp = Open("$SAMPLE_DATA/Big Class.jmp");
dt_summary = dt_temp << Summary(Group(:age, :sex), Freq("None"), Weight("None"), output table name( "summary" ));
dt_summary << Select Where(:N Rows >= 3 & !Contains(:sex, "M"));

//get rows which should be set to empty
rows = dt_temp << Get Selected Rows;
//get all values in name column
colValues = dt_temp:name << get values; 
show(colValues);
//set selected rows in the colValues list to ""
colValues[rows] = ""; 
show(colValues);
dt_temp:name << Set Values(colValues);

Re: Replace multiple rows value from one column is not working properly

txnelson — Sat, 05 Jun 2021 17:09:37 GMT

Try closing the summary table before setting the values in the original table. The Summary table is linked to the original and is keeping it from being updated.

dt_temp = data table("aaaa");

dt_summ = dt_temp << Summary( Group( :Column1,:Column2), Freq( "None" ), Weight( "None" ),output table name( "summary" ) );
data table("summary") << select where (:N rows >= 2 & !contains(:Column1,"AA") & !contains(:Column1,"BB") );
rows = dt_temp << get selected rows; 
close( dt_summ, nosave );


dt_temp:Column3[rows] = "";
//Column(dt_temp, "Column3")[rows] = "";

Re: Replace multiple rows value from one column is not working properly

nthai — Sat, 05 Jun 2021 17:35:38 GMT

Thank you Jim and Jarmo! Both solutions works like a charm for me!