I am trying to create a script to add a math column to sum multiple columns with a same suffix in a big data table. And these columns have special character, e.g. %,[,],etc., which broke some existing solutions in the forum.
For example,
Out of 100 columns, there are 4 columns named: Class0 Percent%, Class1 Percent%, Class2 Percent%, Class3 Percent%
Here is a script I attempted to write:
dt = Current Data Table();
col_list = dt << get column names( string );
For( i = N Items( col_list ), i > 0, i--,
If( !Contains( col_list[i], "Percent%" ),
Remove From( col_list, i )
)
);
new_formula = "SUM(";
For( i = N Items( col_list ), i > 0, i--,
new_formula = new_formula || "\[:Name( "]\" || col_list[i] || "\["), ]\";
);
new_formula = new_formula || ")";
dt << New Column( "Sum Class",
Numeric,
Continuous,
Formula( Name Expr( new_formula ) )
);
And the result formula is:
"SUM(:Name( \!"Class3 Percent%\!"), :Name( \!"Class2 Percent%\!"), :Name( \!"Class1 Percent%\!"), :Name( \!"Class0 Percent%\!"), )"
How can I get rid of the \! ?? Thanks!
