## Applying one formula to many sequencing type of columns

Community Trekker

Joined:

Sep 29, 2014

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

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)));

);

2 REPLIES

Community Trekker

Joined:

Sep 29, 2014

Any ideas?

Super User

Joined:

Jun 23, 2011

Solution

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)));

);