topic Re: Deleting a row based on the presence of specific words in a cell in Discussions

Deleting a row based on the presence of specific words in a cell

BladeRunner — Wed, 14 Aug 2024 00:10:28 GMT

Hello All! I think this might be a common situation for many working with data files from various instruments. My data sets are derived from stitching multiple output files from a specific instrument. Besides the useful data rows, there are a number of rows on top of each output file that specify the Recipe, File, and others, typically, this info is in Column 1. For processing the data, I do not need that info, so I want to delete the unnecessary rows containing the above words Recipe, File, etc. in the cells in Column 1. Note that there might be other words in the same cells, for example: "Wafer Recipe Name", or "Stage Group File", etc. I tried a number of permutations on the following, but I always get an error message saying: "Send expects scriptable objects in access of evaluation of "Send", etc. Many thanks in advance!

dt_new2 << get rows where (contains (:Column 1, "Recipe"));
dt_new2 << delete rows;
Wait(1);

dt_new2 << get rows where (contains (:Column 1, "File"));
dt_new2 << delete rows;
Wait(1)

Re: Deleting a row based on the presence of specific words in a cell

txnelson — Wed, 14 Aug 2024 00:35:14 GMT

Try this

Names Default To Here( 1 );
dt_new2 = Current Data Table();
dt_new2 << select where( Contains( :Column 1, "Recipe" ) );
dt_new2 << delete rows;
Wait( 0 );

dt_new2 << select where( Contains( :Column 1, "File" ) );
dt_new2 << delete rows;

The Scripting Index

Help=>Scripting Index

is a great source of statement syntax and examples.

Also, I advise you to take the time to read through the Scripting Guide found in the JMP Help

Re: Deleting a row based on the presence of specific words in a cell

jthi — Wed, 14 Aug 2024 04:08:25 GMT

If you wish to just use << Delete Rows without any arguments, you have to select rows (like Jim did show). Other option is to collect your rows into a variable and use that (my preferred method as it skips "unnecessary" row selection but selection is more visual if you wish to debug)

Names Default To Here(1);

rows_to_delete = dt_new2 << get rows where(Contains(:Column 1, "Recipe"));
dt_new2 << delete rows(rows_to_delete); 

rows_to_delete = dt_new2 << get rows where(Contains(:Column 1, "File"));
dt_new2 << delete rows(rows_to_delete);

This behaviour is not documented in Scripting Index,

but it can be found from JSL Syntax Reference Data Table Messages (jmp.com) and Scripting Guide Delete Rows (jmp.com) (search Delete Rows from Script Editor and press Topic Help)

Re: Deleting a row based on the presence of specific words in a cell

BladeRunner — Wed, 14 Aug 2024 23:11:13 GMT

Hi, txnelson, thank you for your reply! Like I said, I tried a bunch of related commands, including select where + contains, but without the statement Names default to here at the top, that approach did not work previously. The routine works for me now, many thanks for the input. Cheers!

Re: Deleting a row based on the presence of specific words in a cell

BladeRunner — Wed, 14 Aug 2024 23:15:13 GMT

Hi jthi, thank you for your reply, I appreciate it. I always remind myself to rely more on variables, but unfortunately, I do it only when there are no other options. A great suggestion, I am sure it will be useful to many others. Cheers!

Re: Deleting a row based on the presence of specific words in a cell

jthi — Thu, 15 Aug 2024 05:47:38 GMT

You can also do it without variables but I wouldn't suggest it as it is much more difficult to read and harder to debug if there are any issues

Names Default To Here(1);

dt_new2 << delete rows(dt_new2 << get rows where(Contains(:Column 1, "Recipe"))); 
dt_new2 << delete rows(dt_new2 << get rows where(Contains(:Column 1, "File")));