cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Submit your abstract to the call for content for Discovery Summit Americas by April 23. Selected abstracts will be presented at Discovery Summit, Oct. 21- 24.
Discovery is online this week, April 16 and 18. Join us for these exciting interactive sessions.
Choose Language Hide Translation Bar
ngodfrey
Level II

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
txnelson
Super User

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

View solution in original post

2 REPLIES 2
txnelson
Super User

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
ngodfrey
Level II

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

That worked beautifully! Thank you!