turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Sum all Columns that contain a string in the column name

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 5, 2013 4:02 PM
(3277 views)

I'm trying to take a data table and add a column with a formula that sums all columns that contain the string "ToAdd" in the column name. I'm trying to script this because the columns that will have "ToAdd" somewhere in the name will change.

So far, I have the script that can collect all the column names into a list, but I'm not sure how to then use that list to generate a new column with a formula that sums all those columns together. This seemed like the best way to start, but not sure where to go from here (or if it really is a good way to start)

dt = current data table();

N = N Col( dt );

AddColumns = {};

For( i=1, i <= N, i++,

colname = Column(i) << getname();

If( Contains( colname, "ToAdd") > 1,

Insert Into(AddColumns, colname )

);

)

);

Thanks for any help in advance!

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

It's a good start. To set column formulas by script, however, is a bit tricky as the argument is interpreted literally. Building a formula dynamically usually involves building strings or expressions that finally is parsed/evaluated. Otherwise local or session-specific variables will end up in the formula and it will not work as expected. At least that is my experience.

Here's an example using the string approach:

dt = Current Data Table**()**;

N = N Col**(** dt **)**;

s = "";

For**(** i = **1**, i <= N, i++,

colname = Column**(** i **)** << getname**()**;

If**(** Contains**(** colname, "ToAdd" **)** > **0**,

s = s || If**(** Length**(** s **)** > **0**, ", ", "" **)** || colname

**)**;

**)**;

show**(**s**)**;

col = dt << New Column**(** "Sum(AddColumns)", numeric **)**;

Eval**(** Parse**(** Eval Insert**(** "col<<set formula(sum(^s^))" **)** **)** **)**;

2 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

It's a good start. To set column formulas by script, however, is a bit tricky as the argument is interpreted literally. Building a formula dynamically usually involves building strings or expressions that finally is parsed/evaluated. Otherwise local or session-specific variables will end up in the formula and it will not work as expected. At least that is my experience.

Here's an example using the string approach:

dt = Current Data Table**()**;

N = N Col**(** dt **)**;

s = "";

For**(** i = **1**, i <= N, i++,

colname = Column**(** i **)** << getname**()**;

If**(** Contains**(** colname, "ToAdd" **)** > **0**,

s = s || If**(** Length**(** s **)** > **0**, ", ", "" **)** || colname

**)**;

**)**;

show**(**s**)**;

col = dt << New Column**(** "Sum(AddColumns)", numeric **)**;

Eval**(** Parse**(** Eval Insert**(** "col<<set formula(sum(^s^))" **)** **)** **)**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Works like a charm!

Thanks,

Daniel