Community Trekker

Joined:

Sep 29, 2014

## Applying one formula to many sequencing type of columns

Hi all,

I come accross many times with this question;

Applying one type of formula to many newly generated columns. Could someone please explain the main logic behind it?

For example:

1. I have a data set of 72 years with 12 months. I would like to set one of the year/month to be the best and accept it as =100 and I would like to compare all others to that year/month in percentage (particularly revenue increase or decrease in percentage).

2. I have 1 column to think as MainColumn with 100 other columns (1,2,3,4,5,...) in a data table, and with adding 100 new columns I would like to use the same formula for each: NewColumn1 = Column1/MainColumn, NewColumn2=Column2/MainColumn...etc...

3. I have columns in paired relations (Column1 Column2, Column3 Column4) and would like to create new column (ColumnNew1) with a formula related to paired columns (=Column1+Column2), (ColumnNew2=Column3+Column4)...and so on.

How to do these?

Many thanks

1 ACCEPTED SOLUTION

Accepted Solutions

Super User

Joined:

Jun 23, 2011

Solution

## Re: Applying one formula to many sequencing type of columns

I assume you looking for a scripting solution. Below are some ideas.

``````//Example tables
dt1 = New Table("example 1",
New Column("year", numeric, Formula(Sequence(1944, 2015, 1, 12))),
New Column("month", numeric, values(Repeat(1 :: 12, 72))),
New Column("revenue", numeric, Formula(Random Integer(10000)))
);
dt2 = New Table("example 2", add rows(5), New Column("main column", numeric, values(1 :: 5)));
dt2 << add multiple columns("column", 10, after last, numeric);
For(i = 2, i <= 11, i++,
Column(dt2, i) << set formula(Random Integer(10))
);

// 1)
dt1 << New Column("revenue, % of year's best month", numeric, Format("percent"), Formula(:revenue / Col Max(:revenue, year)));

// 2) and 3) The trick is to use Eval Expr() and Name Expr() when defining formulas.

cols = dt2 << get column names;

For(i = 2, i <= 11, i++,
colname = Char(cols[i]) || " by main";
f = Eval Expr(Expr(cols[i]) / :main column);
dt2 << New Column(colname, numeric, formula(Name Expr(f)));
);

// dito for 3
For(i = 2, i <= 6, i = i + 2,
colname = Char(cols[i]) || "+" || Char(cols[i + 1]);
f = Eval Expr(Expr(cols[i]) + Expr(cols[i + 1]));
dt2 << New Column(colname, numeric, formula(Name Expr(f)));
);``````
3 REPLIES

Community Trekker

Joined:

Sep 29, 2014

Any ideas?

Super User

Joined:

Jun 23, 2011

Solution

## Re: Applying one formula to many sequencing type of columns

I assume you looking for a scripting solution. Below are some ideas.

``````//Example tables
dt1 = New Table("example 1",
New Column("year", numeric, Formula(Sequence(1944, 2015, 1, 12))),
New Column("month", numeric, values(Repeat(1 :: 12, 72))),
New Column("revenue", numeric, Formula(Random Integer(10000)))
);
dt2 = New Table("example 2", add rows(5), New Column("main column", numeric, values(1 :: 5)));
dt2 << add multiple columns("column", 10, after last, numeric);
For(i = 2, i <= 11, i++,
Column(dt2, i) << set formula(Random Integer(10))
);

// 1)
dt1 << New Column("revenue, % of year's best month", numeric, Format("percent"), Formula(:revenue / Col Max(:revenue, year)));

// 2) and 3) The trick is to use Eval Expr() and Name Expr() when defining formulas.

cols = dt2 << get column names;

For(i = 2, i <= 11, i++,
colname = Char(cols[i]) || " by main";
f = Eval Expr(Expr(cols[i]) / :main column);
dt2 << New Column(colname, numeric, formula(Name Expr(f)));
);

// dito for 3
For(i = 2, i <= 6, i = i + 2,
colname = Char(cols[i]) || "+" || Char(cols[i + 1]);
f = Eval Expr(Expr(cols[i]) + Expr(cols[i + 1]));
dt2 << New Column(colname, numeric, formula(Name Expr(f)));
);``````

Occasional Contributor

Joined:

Mar 7, 2018

## Re: Applying one formula to many sequencing type of columns

Very helpful for some scripting I was doing for Monte Carlo analysis. Thank you!