cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
Miof
Level II

Creating formula using dynamic variables in for loop

Hi All
I did that one time, and it was painfully but I lost the script and the memory of that. Below what I am trying to do:

Tweak_List = dt_Temp << Get Column Names("String");
For(i = 1, i <= N Items(Tweak_List), i++, 
 
	a = Left(Tweak_List[i], Length(Tweak_List[i]) - 1) || "Δ";
	b = Tweak_List[i];
	c = Left(Tweak_List[i], Length(Tweak_List[i]) - 2);
 
	dt << New Column(Left(Tweak_List[i], Length(Tweak_List[i]) - 1) || "Δ", Numeric, Continuous, Formula(If(:name1 == :name2, c - b, 0)));
);
 
In dt I have columns named ColName_2 and ColName, the Tweak_List is the list of names of columns like ColName_2.
I need do create a new Delta Column named ColName_Δ for each pair and have the formula  ColName - ColName_2 in it is a condition is meet.
I remember I was working with Eval and Co, but I forgot. Pls help me
 
2 REPLIES 2
Miof
Level II

Re: Creating formula using dynamic variables in for loop

And as usual, finally I got one solution, fell free to replay with more efficient script.

For(i = 1, i <= N Items(Tweak_List), i++, 
 
	Eval(
		Eval Expr(
			dt << New Column(Left(Tweak_List[i], Length(Tweak_List[i]) - 1) || "Δ",
				Numeric,
				"Continuous",
				Formula(
					If(:ENTITY == :ENTITY_2,
						As Column(Expr(Char(Left(Tweak_List[i], Length(Tweak_List[i]) - 2)))) - As Column(Expr(Char(Tweak_List[i]))),
						0
					)
				)
			)
		)
	);
 
);
 
jthi
Super User

Re: Creating formula using dynamic variables in for loop

You could use Word() or Words() instead of Left().

Names Default To Here(1);

dt = New Table("Untitled 143",
	Add Rows(1),
	Compress File When Saved(1),
	New Column("Column1", Numeric, "Continuous", Format("Best", 12), Set Values([1])),
	New Column("Column1_2", Numeric, "Continuous", Format("Best", 12), Set Values([2])),
	New Column("Column2", Numeric, "Continuous", Format("Best", 12), Set Values([3])),
	New Column("Column2_2", Numeric, "Continuous", Format("Best", 12), Set Values([4]))
);

tweaklist = {"Column1_2", "Column2_2"};

For Each({col_name}, tweaklist,
	Eval(EvalExpr(
		dt << New Column(Word(1, col_name, "_") || "_Δ", Numeric, Continuous, Formula(
			Expr(Name Expr(As Column(col_name))) - Expr(Name Expr(As Column(Word(1, col_name, "_"))))
		));
	));
);

Also if you don't need the formula but just the values, you could use << Set Each Value which doesn't need as heavy evaluation 

For Each({col_name}, tweaklist,
	dt << New Column(Word(1, col_name, "_") || "_Δ", Numeric, Continuous, << Set Each Value(
		As Column(col_name) - As Column(Word(1, col_name, "_"))
	));
);
-Jarmo