topic Re: I can't even do this simple replace! in Discussions

I can't even do this simple replace!

lwx228 — Mon, 03 Sep 2018 10:36:13 GMT

Not found in the script index, please finish with JSL.Thank you very much!

Re: I can't even do this simple replace!

ian_jmp — Mon, 03 Sep 2018 12:36:21 GMT

NamesDefaultToHere(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");
Wait(3);
maleRows = Loc(dt[0, "sex"], "M");
dt[maleRows, "sex"] = "boy";

Re: I can't even do this simple replace!

lwx228 — Mon, 03 Sep 2018 13:33:44 GMT

It seems to be quite different from the VBA approach.thank you!

Re: I can't even do this simple replace!

lwx228 — Mon, 03 Sep 2018 13:46:48 GMT

I've tried to write JSL in a way that only works with perfectly matched substitutions, but if you don't want to replace only a few characters, how do you write JSL?Thank you!

Re: I can't even do this simple replace!

txnelson — Mon, 03 Sep 2018 14:06:47 GMT

This is one way to do it

dt = Current Data Table();
dt:class[dt << get rows where( dt:class == "A1" )] = "A9";

Re: I can't even do this simple replace!

lwx228 — Mon, 03 Sep 2018 14:19:59 GMT

If this is the new case, this JSL is not very efficient.Can the code be improved for the new instance?Thank you!

Re: I can't even do this simple replace!

ian_jmp — Mon, 03 Sep 2018 14:37:58 GMT

If I understand correctly, consider using the 'Match()' JSL expression. I generated the code below by using 'Cols > Recode':

and inspecting the JSL for the resulting formula column. And, 'yes', JSL is very different from VBA. You should look at 'Help > Books > Scripting Guide' and/or consider some training.

NamesDefaultToHere(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");
Wait(3);
dt << New Column( "Age 2",
					Character,
					"Ordinal",
					Formula(
						Match( :age,
							12, "Twelve",
							13, "Thirteen",
							14, "Fourteen",
							15, "Fifteen",
							16, "Sixteen",
							17, "Seventeen",
							Format( :age, "Fixed Dec", 5, 0 )
						)
					)
				);

Re: I can't even do this simple replace!

lwx228 — Mon, 03 Sep 2018 14:53:23 GMT

I'm so sorry!Maybe the example I gave is not appropriate and misleading.

I just want to replace some of the specified characters with others in the same column.That is the manual interface function, just want to use JSL implementation.

The VBA implementation in excel is simple:Selection.Replace What:="1", Replacement:="9", LookAt:=xlPart

I just want the effect of this simple trade effect.But I don't have the corresponding code in JSL.Thank you!

Re: I can't even do this simple replace!

lwx228 — Mon, 03 Sep 2018 14:59:39 GMT

This is the result of the substitution in the case of "Match entire cell value" the multiple checkbox is not selected.Thank you!

Re: I can't even do this simple replace!

lwx228 — Mon, 03 Sep 2018 15:02:37 GMT

It's just a matter of replacing some characters in a cell with JSL.

Re: I can't even do this simple replace!

ian_jmp — Mon, 03 Sep 2018 15:24:04 GMT

OK. So maybe take a look at regular expressions.

Re: I can't even do this simple replace!

lwx228 — Mon, 03 Sep 2018 23:31:55 GMT

I see. That's all I can do.Thankyou!

Re: I can't even do this simple replace!

txnelson — Tue, 04 Sep 2018 00:14:12 GMT

That is not all the ways it can be coded in JSL to accomplish what you want, but Ian's suggestion is what I would have also suggested as the solution.

Re: I can't even do this simple replace!

lwx228 — Tue, 04 Sep 2018 00:37:13 GMT

Thanks for your help.
I want to realize that VBA is done with one click In JMP: you can do many complicated operations with just one click of the home-made button.

Re: I can't even do this simple replace!

gzmorgan0 — Wed, 05 Sep 2018 02:40:22 GMT

Iwx228 brings up an interesting wish list item, albeit the example provided is not the typical JSL use case, where there are user selected rows.

Excel VBA has a spreadsheet method (function) to do a global replace. JMP's Substitute() and Substitute Into() functions would meet this users needs without the overhead of regular expressions, since there was no requirement for a pattern, just global replacement. All that is missing is to get and apply it to the current selections. The logical syntax would be

dt << Replace( searchStr, replaceStr, arguments for selections either select rows and selected cols or all, or.....)

Below is a prototype script called ReplaceCell() with usage examples.

A few items to note:

Applies to the current data. Aborts if no current data table.
Aborts if no selected columns. If no selected rows, all rows in the selected columns are used.
As written this function only applies to Character columns; it can be extended to Numeric columns, by using char() and num() functions.
Replaces by row, so looping happens once.
This could have been done by column fairly quicky with LFunc() which I posted previously, but wanted to keep ths simple.
Use Include() for JSL. No need to make it an Add-In since CTRL + F already does this

ReplaceCell = Function({src,rpl},{cdt, scol, srow, i, j, val},
 if(ntable()>0, cdt=current data table(), throw("no table available"));
 scol = cdt << get selected columns;
 if(nitems(scol) ==0, throw("no columns are selected...aborting"));
 srow = cdt << get selected rows();
 if (nrow(srow)==0, srow = Transpose(1::nrow(cdt)) ); //if no rows are selected, assume the entire column.
 for(i=1, i<=nrow(srow), i++,
 for(j=1, j<=nitems(scol), j++,
 val = scol[j][srow[i]];
 if( IsString(val), scol[j][srow[i]] = Substitute(val, src, rpl) ) 
 );
 )//end for i
 
); //end function

//test cases create a new table with 2 selected columns 
ndt = New Table( "Test 1",
 Add Rows( 10 ),
 New Column( "A",
 Character,
 "Nominal",
 Set Values(
 {"A7", "B7", "C7", "D7", "E7", "F7", "G7", "A9", "A79", "A707"}
 )
 ),
 New Column( "B",
 Character,
 "Nominal",
 Set Values(
 {"M7", "M77", "M07", "MX7", "E7", "E2", "E3", "F9", "G27", "M99"}
 )
 )
);

//replace all 7's with 1's
ReplaceCell("7","1"); //aborts no selected columns

ndt:A << set selected(1);
ndt:B << set selected(1);

//replace all 7's with 1's
ReplaceCell("7","1");
ndt << bring window to front;

//now try it with selected rows

ndt << select rows([1,2,4,7,9,10]);

ReplaceCell("1","6");
ndt << bring window to front;