JSL: How to copy the last term in a formula and add this value to the same formula

[#1]

Hi everyone,

I'm looking for a script which allows me to select first multiple formula columns and in each formula column it takes the last numeric term and add this value to the already existing formula.

For illustration purpose:

One formula column before running the script:

and afterwards (simply the value 39,78... was copied and added):

Since I have hundred of different formula columns it would be great to have an automated script here.

Looking forward for your help!

Thanks!

/Marco

[#2 · Accepted solution]

Here are two ways to do this, by manipulating the formula as a string and then as an expression:

Names Default To Here( 1 );

//Make some sample data
dt =New Table( "Untitled",
	Add Rows( 100 )
);

For Each( {i}, 1 :: 6,
	Eval(
		Eval Expr(
			dt << New Column( "ToFix " || char(i),
				Expression,
				"None",
				Formula( Vec Quadratic( [1 3 5, 3 2 6, 5 6 1], [1 3 5, 2 4 6] ) * Expr( i ) )
			)
		)
	)
);

// Select some columns
dt << Select Columns( (dt << Get Column References)[4::5] );

//'fix' each selected column formula by manipulating the formula as a string
for each( {col, c}, dt << Get Column References( dt << Get Selected Columns ),
	if( Starts With( col << get name(), "ToFix"),
		newf = regex(
			char(col << Get Formula),
			"(.* \* (\d+))",
			"\1 + \2"
		);
		//show(Parse(newf));
		Eval( Eval Expr(
			col << Set Formula( Expr( Parse(newf) ) )
		) );
	)
);

// Select some different columns
dt << Select Columns( (dt << Get Column References)[6::7] );

//'fix' each selected column formula by manipulating the formula as an expression
for each( {col, c}, dt << Get Column References( dt << Get Selected Columns ),
	if( Starts With( col << get name(), "ToFix"),
		f = col << Get Formula;
		v = Arg(col << Get Formula, 2);
		//show(Name Expr(f));
		newf = Eval Expr( Add(Expr(Name Expr(f)), Expr(Name Expr(v))));
		show(newf);
		Eval( Eval Expr(
			col << Set Formula( Expr( Name Expr(newf) ) )
		) );
	)
);

[#3]

Thanks a lot for your solution @ih !

I had to adapt the regexp slightly to fulfill my needs, but your post was definitely the key to find it!

For any other user, please find below the adapted code:

// Select some columns
dt = currentdatatable();
dt << Get Selected Columns;



//'fix' each selected column formula by manipulating the formula as a string
for each( {col, c}, dt << Get Column References( dt << Get Selected Columns ),
		newf = regex(
			char(col << Get Formula),
			"(.* \* (\d+\.?\d*))",
			"\1 + \2"
		);
		//show(Parse(newf));
		Eval( Eval Expr(
			col << Set Formula( Expr( Parse(newf) ) )
		) );
);

[#4]

You might also be able to use Arg() depending on your formulas. Below if very simple example:

Names Default To Here(1);

dt = New Table("Untitled 2",
	Add Rows(2),
	New Column("Column 1", Numeric, "Continuous", Format("Best", 12), Set Values([1, 2])),
	New Column("Column 2", Numeric, "Continuous", Format("Best", 12), Formula(:Column 1 * 2)),
	New Column("Column 3", Numeric, "Continuous", Format("Best", 12), Formula(:Column 2 * 3))
);

cur_col_idx = 3;

Show(column(dt, cur_col_idx) << get formula);

Eval(Substitute(
			Expr(
				Column(dt, cur_col_idx) << Set Formula(_old_f_ + _new_mult_)
			),
		Expr(_old_f_), Column(dt, cur_col_idx) << Get Formula,
		Expr(_new_mult_), 
			old_f = Column(dt, cur_col_idx - 1) << Get Formula; 
			Arg Expr(old_f, N Arg(old_f))
));

Show(column(dt, cur_col_idx) << get formula);