cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
vinkane
Level III

Regex within a column loop formula

Trying in vain to have theis column formula execute correctly

For( i = 0, i < 10, i++,

Regex( :Source, :Words to Remove, "", global replace )

);

Ten words to remove from the source column and replaced with a null.

All help is appreciated

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Brown
Super User (Alumni)

Re: Regex within a column loop formula

VinKane​ , a point of interest....since column formulas implicitly loop over all rows in a table, I've often used a little trick when I want to script JSL in a column formula. So you in your case, the column formula would look something like this:

12044_colfmlalooping.png

PDB

View solution in original post

5 REPLIES 5
Craige_Hales
Super User

Re: Regex within a column loop formula

The for loop needs to start at 1.  the regex function needs to work with a JSL variable, it can't use the column.  You might want to use a table variable to hold the list of words to delete rather than keeping them in a column.  the JSL could be a lot simpler...see second example.

example 1

New Table( "Untitled 1",  Add Rows( 10 ), New Column( "words to remove", Character, "Nominal",

      Set Values(

        {"aaa", "bbb", "ccc", "ddd", "eee", "fff", "ggg", "hhh", "iii", "jjj"}

      )

  ),

  New Column( "source", Character, "Nominal",

      Set Values(

        {"the aaa is bbb and bbb ccc", "a aaa b bbb c ccc ccc", "b bbb c ccc", "",

        "", "", "", "", "", ""}

      )

  ),

  New Column( "redacted", Character, "Nominal",

      Formula(

        x = :source;

        For( i = 1, i <= 10, i++,

            x = Regex( x, :words to remove, "", GLOBALREPLACE ),

        );

        x;

      )

  )

);

12038_pastedImage_58.png

example 2

New Table( "Untitled 2",  Add Rows( 10 ),

  New Table Variable( "redactionlist", "aaa|bbb|ccc|ddd|eee|fff|ggg|hhh|iii|jjj" ),

  New Column( "source", Character, "Nominal",

      Set Values(

        {"the aaa is bbb and bbb ccc", "a aaa b bbb c ccc ccc",

        "fred iii ralph bbb bbb", "a aaa b bbb", "", "", "", "", "", ""}

      )

  ),

  New Column( "redacted", Character, "Nominal",

      Formula( Regex( :source, :redactionlist, "", GLOBALREPLACE ) )

  )

)

12037_pastedImage_31.png

Craige
vinkane
Level III

Re: Regex within a column loop formula

!Thanks, works like a charm


Phil_Brown
Super User (Alumni)

Re: Regex within a column loop formula

VinKane​ , a point of interest....since column formulas implicitly loop over all rows in a table, I've often used a little trick when I want to script JSL in a column formula. So you in your case, the column formula would look something like this:

12044_colfmlalooping.png

PDB
vinkane
Level III

Re: Regex within a column loop formula

Great suggestion and hint. As a newbee I'm trying to absorb all of this

Phil_Brown
Super User (Alumni)

Re: Regex within a column loop formula

VinKane​, my pleasure. I should add that this method is useful for relatively simple scripts/formula. As requirements grow, you eventually are better off doing everything in pure JSL, as column formulas can get a bit tedious to edit/maintain.

PDB