405662_en-US

liliconcom

How to perform "find & replace" in specific a column using JSL script?

Hi,

How can I re-create the "Find & replace" operation on a specific column by JSL script?

 

For example - I want to replace "K" with "1e3" & "M" with "1e6" in "Count" column

(to convert it to numeric type later).

I didn't find a simple way to do it without using a loop (there so it would be fast when many rows needs to be checked)

Thanks

ScriptingRe: How to perform "find & replace" in specific a column using JSL script?

I think Recode can do this:

 

Other way would be creating new column with some sort of column:

conversionAa = ["K" => "1e3", "M" => "1e6"];
If(Contains(conversionAa, Right(:Count, 1)),
 Substitute(:Count, Right(:Count,1), conversionAa[Right(:Count, 1)])
);

My suggestion would be to use Recode and if you need the script you can get it from the Recode platform:

Names Default To Here(1);
dt = Data Table("Untitled");
dt << Begin Data Update;
col1 = dt << New Column(dt:Count);
col1 << Set Name("Count 2");
dt << Move Selected Columns({col1}, after(dt:Count));
dt << Recode Column(
 dt:Count,
 {Substitute(_rcNow, "K", "1e3"), Substitute(_rcNow, "M", "1e6")},
 Target Column(col1)
);
dt << End Data Update;

Re: How to perform "find & replace" in specific a column using JSL script?

@DonMcCormack 

The big win on a lot of the ways you might do this will be to use the Begin/End Data Update as @jthi showed. Here's another way to do it:

dt = Open( "$sample_data/big class.jmp" );
dt << Begin Data Update;
For Each Row( dt,
 dt:name = Substitute( dt:name, 
  "A", "a",
  "E", "e",
  "I", "i",
  "O", "o",
  "U", "u"
 )
);
dt << End Data Update;

Lower case vowels.

 

Re: How to perform "find & replace" in specific a column using JSL script?Thanks a lot !!Re: How to perform "find & replace" in specific a column using JSL script?

Thanks @Craige_Hales. Looks like the makings for a good Challenge question.