topic Re: JSL equivalent for "select matching cells" in Discussions

JSL equivalent for "select matching cells"

BHarris — Tue, 22 Jan 2019 20:28:40 GMT

Is there a JSL equivalent for "select matching cells" in a given column?

For example, I have many simulation runs represented in a single table. Sometimes I see a rogue data point in Graph Builder, and I want to see all the other points from that run, which is all points with the same "run id". Is there a way in JSL to select those points, then expand the selection to all rows with the same value in the "run id" column as the points I selected?

Re: JSL equivalent for "select matching cells"

GM — Tue, 22 Jan 2019 22:05:49 GMT

Hello,

You will find JSL examples by searching the Scripting Index with "Select Where". The examples in the Scripting Index should get you started. You will need to be able to specify the criteria you are using to identify the rouge data points in graph builder. Hope this is helpful.

Re: JSL equivalent for "select matching cells"

gianpaolo — Mon, 28 Jan 2019 15:44:08 GMT

in JMP --> HELP--> SCRIPTING INDEX you can find examples..here the ones for 'select where'. hope can help you

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << Select Where( :Age < 14 );

Re: JSL equivalent for "select matching cells"

BHarris — Mon, 28 Jan 2019 15:37:03 GMT

I didn't realize when I wrote this that JMP supports setting keyboard shortcuts for menu items. I set ctrl-shift-m to activate the "select matching cells", and it works perfectly for what I needed.

I assume the responses below would be correct for the original question.

Re: JSL equivalent for "select matching cells"

GM — Mon, 28 Jan 2019 17:23:43 GMT

Nice one, that's a cool solution.

Sincerely,

Re: JSL equivalent for "select matching cells"

Devin — Fri, 27 Aug 2021 23:43:35 GMT

I see you found the keyboard shortcut - did you ever find a jsl equivalent for ctrl-shift-m, selecting matching cells? Thx

Re: JSL equivalent for "select matching cells"

jthi — Sat, 28 Aug 2021 06:48:49 GMT

At least JMP16.1 Scripting index provides following:

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");
dt << Select Rows([1, 2, 3, 4]);
dt << Go To(:Height);
Wait(2);
dt << Select Matching Cells();

Re: JSL equivalent for "select matching cells"

dadawasozo — Fri, 05 Nov 2021 21:32:22 GMT

Hi jthi,

I m looking for solution to select first row from column 2 to 193 before the select matching cells.

Do you think you can help?

Re: JSL equivalent for "select matching cells"

txnelson — Fri, 05 Nov 2021 23:51:34 GMT

I am not sure what exactly you are looking for, but maybe this will give you a possible feature you can use.

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");
dt << select where( row() < 24 );

Re: JSL equivalent for "select matching cells"

dadawasozo — Sat, 06 Nov 2021 14:07:23 GMT

Hi Jim,

I have 200 columns. what I want to do :
1) select first row across specific set of columns from column 2 to column 193.
2) then apply select matching cell ()

3) last, delete all these matching cells that is across column 2-193.

below will work if I able to make the Go to (Column(2::193)). any suggestion?

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");
dt << Select Rows([1, 2, 3, 4]);
dt << Go To(:Height);
Wait(2);
dt << Select Matching Cells();

Re: JSL equivalent for "select matching cells"

txnelson — Sat, 06 Nov 2021 15:31:19 GMT

Here is a script that will do what I think you need

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );
selectionRows = [1, 2, 3, 4];

For( i = 4, i <= 5, i++,
	dt << clear rowstates;
	dt << Select Rows( selectionRows );
	dt << Go To( i );
	dt << Select Matching Cells();
	selectedRows = dt << get selected rows;
	selectedRows = selectedRows[Index( 5, N Rows( selectedRows ) )];
	Column( dt, i )[selectedRows] = .;
);

Re: JSL equivalent for "select matching cells"

dadawasozo — Sat, 06 Nov 2021 16:37:08 GMT

Hi Jim ,

I got error below with the provided. I want to use picture to describe my problem here. please see the next pictures (table) as example. how can I do that with code?

below is my example of the structure of the table. I want to remove empty cell with criteria of only if column2 to column8 are all empty cell. if any of the cell in this range of columns is not empty then do not remove. When I do it with mouse click, I will first (a) highlight first row but only on column2-column8. then I right click to select matching cell (b) and follow with delete (c).

(a) (b)

(c)

Re: JSL equivalent for "select matching cells"

txnelson — Sat, 06 Nov 2021 20:44:18 GMT

Is this what you are looking for?

Names Default To Here( 1 );
dt = New Table( "blood pressure",
	Add Rows( 20 ),
	New Column( "Subject", Set Values( [1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 1, 2, 3, 4, 5] ) ),
	New Column( "S_Dose_Col",
		character,
		"Nominal",
		Set Values(
			{"A", "A", "A", "A", "A", "B", "B", "B", "B", "B", "Control", "Control", "Control", "Control",
			"Control", "Placebo", "Placebo", "Placebo", "Placebo", "Placebo"}
		)
	),
	New Column( "S_BP_8M_Col",
		Numeric,
		Set Values(
			[182.595915155309, 172.512940709438, 180.774421425438, 180.620679187184, ., 171.439595422176,
			174.589230394192, 179.109260746268, 177.787439187438, ., 181.804016028632, ., 186.313144806757,
			175.347853139956, 181.103289178403, 181.288352739913, 178.777832744608, 176.763152449944,
			175.768608854708, 184.167672392284]
		)
	),
	New Column( "S_BP_12M_Col",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 5, 0 ),
		Set Values(
			[173.550216520736, 180.718560564297, 188.500473854652, 176.718084823659, ., 181.462075626649,
			180.330100756984, 172.886222586229, 192.050244302843, ., 185.501158177984, 183.877324525276,
			179.931613365546, 180.493132509235, 174.944992509985, 177.663853371957, 174.717037207769,
			186.677919707096, 178.12326354079, 171.230704316198]
		)
	),
	New Column( "BP 6M",
		Numeric,
		Set Values(
			[180.116643270986, 180.917546679043, 177.384986937421, 182.381646560932, ., 179.517397097984, .,
			182.904992672503, 176.785646502378, ., 178.209310795898, 186.575178728884, ., 177.2578428812,
			180.642613381863, 178.850703910826, 188.986597488081, 172.45356859494, 182.404679249467,
			182.335148506991]
		)
	),
	New Column( "BP 8W",
		Numeric,
		Set Values(
			[173.797442210887, 170.201828509985, 188.441549950731, 176.337805749776, ., 183.021068043181,
			183.498290114926, 187.881911714776, 184.924564594592, ., 177.186342937671, ., 171.059060559811,
			184.517143208058, 179.143677395064, 178.211632365764, 190.546847185169, 179.666182707754,
			176.614597739946, 181.334797218108]
		)
	),
	New Column( "BP 12W",
		Numeric,
		Set Values(
			[178.416197148117, 178.839136233913, 175.346240386252, 172.773758285362, ., 175.683661608275,
			179.622232533597, 192.773275959188, 172.499108408691, ., 176.573252982367, 174.72327532089,
			172.873089144473, 171.169474537616, 185.445956950321, 181.685844613992, 178.055520662495,
			182.726891244295, 178.320419876662, 186.395858538357]
		)
	),
	New Column( "BP 6W",
		Numeric,
		Set Values(
			[181.152265967985, 176.084997418848, 181.924943930192, 183.608276048664, ., 174.15485890414,
			172.866924129507, 169.793497264777, 179.464417655732, ., 177.20643071091, 176.115218469266,
			181.319863949352, ., 183.622763290159, 178.639264502305, 176.991735583422, 177.924284755616,
			177.143465976767, 183.421182544757]
		)
	),
	New Column( "BP 8F",
		Numeric,
		Set Values(
			[171.278300921568, 175.161843431476, 183.12980493925, 183.04930217403, ., 184.878981656321,
			184.16481869131, 179.098192996471, 186.765084181627, ., 182.640175499696, 179.80577644674,
			179.038752187802, 175.727537977595, 183.636564430071, 188.483289884327, 182.282596238225,
			183.200306257339, 175.856743742269, 183.517577768589]
		)
	),
	New Column( "BP 12F",
		Numeric,
		Set Values(
			[177.570953487335, 184.884491085702, 182.794049100295, 187.31279033708, ., 182.387718653956,
			184.653326274105, 184.653672194875, 184.384583007231, ., 190.972901519754, 181.46178488602,
			181.459302956398, 184.604441269869, 173.249587056935, 183.125809166649, 187.951725739917,
			190.139279669601, 183.549834391574, 171.907000056173]
		)
	),
	New Column( "BP 6F",
		Numeric,
		Set Values(
			[170.514896659448, 187.603078247176, 180.121927541929, 182.931206367403, ., 184.169322994033,
			180.368840760416, 170.403578559484, 177.427685029706, ., 174.986928330607, 184.070139882305,
			189.242086812316, 169.540004898628, 185.21824057543, 173.182049720424, 170.940897197153,
			177.823031925273, 183.621024712236, 178.774574166082]
		)
	)
);

dt << select where( Is Missing( Sum( dt[Row(), Index( 3, 11 )] ) ) );

Re: JSL equivalent for "select matching cells"

dadawasozo — Sun, 07 Nov 2021 02:54:28 GMT

Hi Jim,

This is working. Just one more question for this topic.
what if one of the columns above are mixed data-type with some columns are character and some numeric? How we can select the columns?

Thanks

Re: JSL equivalent for "select matching cells"

txnelson — Sun, 07 Nov 2021 06:29:44 GMT

You will have to add a check for each character column into the Select Where statement.

Names Default To Here( 1 );
dt = New Table( "blood pressure",
	Add Rows( 20 ),
	New Column( "Subject",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Values( [1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 1, 2, 3, 4, 5] )
	),
	New Column( "S_Dose_Col",
		Character,
		"Nominal",
		Set Values(
			{"A", "A", "A", "A", "A", "B", "B", "B", "B", "B", "Control", "Control",
			"Control", "Control", "Control", "Placebo", "Placebo", "Placebo",
			"Placebo", "Placebo"}
		)
	),
	New Column( "S_BP_8M_Col",
		Numeric,
		"Continuous",
		Format( "Best", 16 ),
		Set Values(
			[182.595915155309, 172.512940709438, 180.774421425438, 180.620679187184,
			., 171.439595422176, 174.589230394192, 179.109260746268,
			177.787439187438, ., 181.804016028632, ., 186.313144806757,
			175.347853139956, 181.103289178403, 181.288352739913, 178.777832744608,
			176.763152449944, 175.768608854708, 184.167672392284]
		)
	),
	New Column( "S_BP_12M_Col",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 5, 0 ),
		Set Values(
			[173.550216520736, 180.718560564297, 188.500473854652, 176.718084823659,
			., 181.462075626649, 180.330100756984, 172.886222586229,
			192.050244302843, ., 185.501158177984, 183.877324525276,
			179.931613365546, 180.493132509235, 174.944992509985, 177.663853371957,
			174.717037207769, 186.677919707096, 178.12326354079, 171.230704316198]
		)
	),
	New Column( "BP 6M",
		Numeric,
		"Continuous",
		Format( "Best", 16 ),
		Set Values(
			[180.116643270986, 180.917546679043, 177.384986937421, 182.381646560932,
			., 179.517397097984, ., 182.904992672503, 176.785646502378, .,
			178.209310795898, 186.575178728884, ., 177.2578428812, 180.642613381863,
			178.850703910826, 188.986597488081, 172.45356859494, 182.404679249467,
			182.335148506991]
		)
	),
	New Column( "BP 8W",
		Character,
		"Nominal",
		Set Values(
			{"173.797442210887", "170.201828509985", "188.441549950731",
			"176.337805749776", "Q", "183.021068043181", "183.498290114926",
			"187.881911714776", "184.924564594592", "", "177.186342937671", "",
			"171.059060559811", "184.517143208058", "179.143677395064",
			"178.211632365764", "190.546847185169", "179.666182707754",
			"176.614597739946", "181.334797218108"}
		)
	),
	New Column( "BP 12W",
		Numeric,
		"Continuous",
		Format( "Best", 16 ),
		Set Values(
			[178.416197148117, 178.839136233913, 175.346240386252, 172.773758285362,
			., 175.683661608275, 179.622232533597, 192.773275959188,
			172.499108408691, ., 176.573252982367, 174.72327532089, 172.873089144473,
			171.169474537616, 185.445956950321, 181.685844613992, 178.055520662495,
			182.726891244295, 178.320419876662, 186.395858538357]
		)
	),
	New Column( "BP 6W",
		Numeric,
		"Continuous",
		Format( "Best", 16 ),
		Set Values(
			[181.152265967985, 176.084997418848, 181.924943930192, 183.608276048664,
			., 174.15485890414, 172.866924129507, 169.793497264777, 179.464417655732,
			., 177.20643071091, 176.115218469266, 181.319863949352, .,
			183.622763290159, 178.639264502305, 176.991735583422, 177.924284755616,
			177.143465976767, 183.421182544757]
		)
	),
	New Column( "BP 8F",
		Numeric,
		"Continuous",
		Format( "Best", 16 ),
		Set Values(
			[171.278300921568, 175.161843431476, 183.12980493925, 183.04930217403, .,
			184.878981656321, 184.16481869131, 179.098192996471, 186.765084181627, .,
			182.640175499696, 179.80577644674, 179.038752187802, 175.727537977595,
			183.636564430071, 188.483289884327, 182.282596238225, 183.200306257339,
			175.856743742269, 183.517577768589]
		)
	),
	New Column( "BP 12F",
		Numeric,
		"Continuous",
		Format( "Best", 16 ),
		Set Values(
			[177.570953487335, 184.884491085702, 182.794049100295, 187.31279033708,
			., 182.387718653956, 184.653326274105, 184.653672194875,
			184.384583007231, ., 190.972901519754, 181.46178488602, 181.459302956398,
			184.604441269869, 173.249587056935, 183.125809166649, 187.951725739917,
			190.139279669601, 183.549834391574, 171.907000056173]
		)
	),
	New Column( "BP 6F",
		Numeric,
		"Continuous",
		Format( "Best", 16 ),
		Set Values(
			[170.514896659448, 187.603078247176, 180.121927541929, 182.931206367403,
			., 184.169322994033, 180.368840760416, 170.403578559484,
			177.427685029706, ., 174.986928330607, 184.070139882305,
			189.242086812316, 169.540004898628, 185.21824057543, 173.182049720424,
			170.940897197153, 177.823031925273, 183.621024712236, 178.774574166082]
		)
	)
);

Names Default To Here( 1 );
dt = New Table( "blood pressure",
	Add Rows( 20 ),
	New Column( "Subject", Set Values( [1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 1, 2, 3, 4, 5] ) ),
	New Column( "S_Dose_Col",
		character,
		"Nominal",
		Set Values(
			{"A", "A", "A", "A", "A", "B", "B", "B", "B", "B", "Control", "Control", "Control", "Control",
			"Control", "Placebo", "Placebo", "Placebo", "Placebo", "Placebo"}
		)
	),
	New Column( "S_BP_8M_Col",
		Numeric,
		Set Values(
			[182.595915155309, 172.512940709438, 180.774421425438, 180.620679187184, ., 171.439595422176,
			174.589230394192, 179.109260746268, 177.787439187438, ., 181.804016028632, ., 186.313144806757,
			175.347853139956, 181.103289178403, 181.288352739913, 178.777832744608, 176.763152449944,
			175.768608854708, 184.167672392284]
		)
	),
	New Column( "S_BP_12M_Col",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 5, 0 ),
		Set Values(
			[173.550216520736, 180.718560564297, 188.500473854652, 176.718084823659, ., 181.462075626649,
			180.330100756984, 172.886222586229, 192.050244302843, ., 185.501158177984, 183.877324525276,
			179.931613365546, 180.493132509235, 174.944992509985, 177.663853371957, 174.717037207769,
			186.677919707096, 178.12326354079, 171.230704316198]
		)
	),
	New Column( "BP 6M",
		Numeric,
		Set Values(
			[180.116643270986, 180.917546679043, 177.384986937421, 182.381646560932, ., 179.517397097984, .,
			182.904992672503, 176.785646502378, ., 178.209310795898, 186.575178728884, ., 177.2578428812,
			180.642613381863, 178.850703910826, 188.986597488081, 172.45356859494, 182.404679249467,
			182.335148506991]
		)
	),
	New Column( "BP 8W",
		Numeric,
		Set Values(
			[173.797442210887, 170.201828509985, 188.441549950731, 176.337805749776, ., 183.021068043181,
			183.498290114926, 187.881911714776, 184.924564594592, ., 177.186342937671, ., 171.059060559811,
			184.517143208058, 179.143677395064, 178.211632365764, 190.546847185169, 179.666182707754,
			176.614597739946, 181.334797218108]
		)
	),
	New Column( "BP 12W",
		Numeric,
		Set Values(
			[178.416197148117, 178.839136233913, 175.346240386252, 172.773758285362, ., 175.683661608275,
			179.622232533597, 192.773275959188, 172.499108408691, ., 176.573252982367, 174.72327532089,
			172.873089144473, 171.169474537616, 185.445956950321, 181.685844613992, 178.055520662495,
			182.726891244295, 178.320419876662, 186.395858538357]
		)
	),
	New Column( "BP 6W",
		Numeric,
		Set Values(
			[181.152265967985, 176.084997418848, 181.924943930192, 183.608276048664, ., 174.15485890414,
			172.866924129507, 169.793497264777, 179.464417655732, ., 177.20643071091, 176.115218469266,
			181.319863949352, ., 183.622763290159, 178.639264502305, 176.991735583422, 177.924284755616,
			177.143465976767, 183.421182544757]
		)
	),
	New Column( "BP 8F",
		Numeric,
		Set Values(
			[171.278300921568, 175.161843431476, 183.12980493925, 183.04930217403, ., 184.878981656321,
			184.16481869131, 179.098192996471, 186.765084181627, ., 182.640175499696, 179.80577644674,
			179.038752187802, 175.727537977595, 183.636564430071, 188.483289884327, 182.282596238225,
			183.200306257339, 175.856743742269, 183.517577768589]
		)
	),
	New Column( "BP 12F",
		Numeric,
		Set Values(
			[177.570953487335, 184.884491085702, 182.794049100295, 187.31279033708, ., 182.387718653956,
			184.653326274105, 184.653672194875, 184.384583007231, ., 190.972901519754, 181.46178488602,
			181.459302956398, 184.604441269869, 173.249587056935, 183.125809166649, 187.951725739917,
			190.139279669601, 183.549834391574, 171.907000056173]
		)
	),
	New Column( "BP 6F",
		Numeric,
		Set Values(
			[170.514896659448, 187.603078247176, 180.121927541929, 182.931206367403, ., 184.169322994033,
			180.368840760416, 170.403578559484, 177.427685029706, ., 174.986928330607, 184.070139882305,
			189.242086812316, 169.540004898628, 185.21824057543, 173.182049720424, 170.940897197153,
			177.823031925273, 183.621024712236, 178.774574166082]
		)
	)
);

dt << select where( Is Missing( Sum( dt[Row(), Index( 3, 11 )] ) ) & dt:BP 8W == ""  );