Subscribe Bookmark RSS Feed

Regex within a column loop formula

vinkane

Community Trekker

Joined:

Dec 23, 2013

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

Joined:

Mar 20, 2012

Solution

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
5 REPLIES
Craige_Hales

Staff

Joined:

Mar 21, 2013

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

Community Trekker

Joined:

Dec 23, 2013

!Thanks, works like a charm


Phil_Brown

Super User

Joined:

Mar 20, 2012

Solution

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

Community Trekker

Joined:

Dec 23, 2013

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

Phil_Brown

Super User

Joined:

Mar 20, 2012

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