Applying one formula to many sequencing type of columns

saitcopuroglu

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

ms

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

 

 

//Example tables
dt1 = New Table("example 1",
    add rows(12 * 72),
    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;
 
// add formulas for 2
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)));
);