52528_en-US

liliconcom

Script to Sum Columns with same sting

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!

Re: Script to Sum Columns with same sting

Thanks Jim for your continuous support!

 

I added/changed the following in bold and it works:

 

new_formula_final = Parse( new_formula );
 dt << New Column( "Sum Class",
  Numeric,
  Continuous,
  Format( "Fixed Dec", 8, 2 ),
  Formula( Name Expr( new_formula_final ) )
 )
);

 

Re: Script to Sum Columns with same sting

If you Parse() the string, it will remove the outside quotes

Re: Script to Sum Columns with same sting

Maybe my question was not clear.

 

The issue is that the fomula of the column ended up witht the whole string as a character column. But I am trying to create a math fomula:

 

SUM(:Name( "Class3 Percent%"), :Name( "Class2 Percent%"), :Name( "Class1 Percent%"), :Name( "Class0 Percent%"), )

 

How can I correct my script to achieve a correct math fomula? Thanks!

Re: Script to Sum Columns with same sting

You don't want to get rid of the \!".  \! is the JMP excape character.  Because the code you have given is a string, the double quotes locacated within the string have to have the excape character in front of each double quote.  When you execute the string, JMP will properly deal with the \!"