Not found in the script index, please finish with JSL.Thank you very much!
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;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.
This is the result of the substitution in the case of "Match entire cell value" the multiple checkbox is not selected.Thank you!
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 )
)
)
);If this is the new case, this JSL is not very efficient.Can the code be improved for the new instance?Thank you!
This is one way to do it
dt = Current Data Table();
dt:class[dt << get rows where( dt:class == "A1" )] = "A9";
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!
You could use data table subscripting:
NamesDefaultToHere(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");
Wait(3);
maleRows = Loc(dt[0, "sex"], "M");
dt[maleRows, "sex"] = "boy";
