Community Trekker

Joined:

Nov 20, 2015

## Set Formula Loop - Using a column string & variable

I have two variables that are used to define 15 columns total. They are a = {"1", "2", "3", "4", "5"} and b = {"1", "2", "3"}. Currently there are 15 columns that are already created with the notation of Column("11"), Column("12"), Column("13"), Column("21"), aka Column(b[1] || a[1]), Column(b[1] || a[2]), etc...

I averaged the first 6 rows of those 15 existing columns and placed it into an array of size 15. I called this array "ave".

So ave[1] would be the average of the first 6 rows in Column("11"), ave[2] would be the average of the first 6 rows in Column("12"), etc...

Now, what I would like to do is create 15 new columns prefixed with "D" using both 'a' & 'b' arrays. Also, have a formula set for those columns that takes the existing column - the average 6 rows of that column.

For example, the formula for Column("D11") would be the following:

:Name("11") - ave[1]

Column("D12") would be the following:

:Name("12") - ave[2]

My Code:

```index = 1;For( i = 3, i > 0, i--,
For( j = 5, j > 0, j--,
at << Add Multiple Columns( "D" || b[i] || a[j], 1, numeric, continuous, );

F = Eval Expr(expr(ave[index]));

Column( "D" || b[i] || a[j] ) << Set Formula( :Name("11")-Name Expr(F) );
index = index + 1;

);
);```

The code above does not work when I attempt to increment the index variable through the 15 averaged values stored in "ave". The other problem is I need to iterate through :Name("11"), :Name("12"), etc. through all combinations of a & b in the formula. I almost need a way to set the formula in the following manner:

:Name(b[i] || a[i])

However, :Name must use a string with quotes between them and won't let you do it that way.

I tried multiple methods with parse, eval, etc. but with no luck.

Any help would be greatly appreciated!

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions

Super User

Joined:

Jun 22, 2012

Solution

## Re: Set Formula Loop - Using a column string & variable

Here is a piece of JSL that will work.  It uses a method where the exact character string of what the required command needs to be is generated, and then Eval(Parse()) to run the code

``````
index = 1;
;
For( i = 3, i > 0, i--,
For( j = 5, j > 0, j--,
Eval(
Parse(
"at << New Column(\!"D" || b[i] || a[j] ||
"\!",1,numeric,continuous,Formula( :Name( \!""
|| Char( index + 10 ) || "\!" ) - " || Char( ave[index] ) || "));"
)
);
index = index + 1;
);
);``````
Jim
2 REPLIES

Super User

Joined:

Jun 22, 2012

Solution

## Re: Set Formula Loop - Using a column string & variable

Here is a piece of JSL that will work.  It uses a method where the exact character string of what the required command needs to be is generated, and then Eval(Parse()) to run the code

``````
index = 1;
;
For( i = 3, i > 0, i--,
For( j = 5, j > 0, j--,
Eval(
Parse(
"at << New Column(\!"D" || b[i] || a[j] ||
"\!",1,numeric,continuous,Formula( :Name( \!""
|| Char( index + 10 ) || "\!" ) - " || Char( ave[index] ) || "));"
)
);
index = index + 1;
);
);``````
Jim

Community Trekker

Joined:

Nov 20, 2015

## Re: Set Formula Loop - Using a column string & variable

That worked beautifully! Thank you!