Set Formula Loop - Using a column string & variable

ngodfrey — Fri, 10 Feb 2017 18:20:26 GMT

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!

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

txnelson — Fri, 10 Feb 2017 19:32:59 GMT

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

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

ngodfrey — Fri, 10 Feb 2017 20:06:29 GMT

That worked beautifully! Thank you!

topic Re: Set Formula Loop - Using a column string & variable in Discussions

Set Formula Loop - Using a column string & variable

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

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