topic Regex within a column loop formula in Discussions

Regex within a column loop formula

vinkane — Fri, 22 Jul 2016 19:39:19 GMT

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

Re: Regex within a column loop formula

Craige_Hales — Wed, 19 Oct 2016 03:25:32 GMT

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 ),

);

)

);

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 ) )

)

Re: Regex within a column loop formula

Phil_Brown — Wed, 19 Oct 2016 03:26:11 GMT

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:

Re: Regex within a column loop formula

vinkane — Mon, 25 Jul 2016 13:35:13 GMT

!Thanks, works like a charm

Re: Regex within a column loop formula

vinkane — Mon, 25 Jul 2016 13:36:45 GMT

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

Re: Regex within a column loop formula

Phil_Brown — Mon, 25 Jul 2016 18:08:08 GMT

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.