Subscribe Bookmark RSS Feed

Column formula using string varialble for column names

I'm trying to create a bunch of new columns with formulas based on existing columns. New column statement is inside For loop and I want to use strings from a list as part column names.
My columns are from the table constructed by Summary function, thus they have "," in the names

InColumn = "qty_in, "||steps[step#];
OutColumn = "qty_out, "||steps[step#];
dt << New Column( Eval("% "||steps[step#]),
numeric,
continuous,
formula( Name(OutColumn) / Name(InColumn) * 100, 1));

steps is a list of strings {"a", "b", "c"} and step# is an integer of For cycle.

What I can not figure out is how to make my OutColumn and InColumn a "quoted string" so formula will work. I've tried Eval and Parse and As Name - nothing worked so far.

Any help will be greatly appreciated.
5 REPLIES
Found the answer:

I had to use

As Column(Eval(InColumn))

in place of a column in the formula
pmroz

Super User

Joined:

Jun 23, 2011

I have a similar problem and can't seem to find the
easiest combination of eval/parse/etc to solve it.
style=""> 
I’m
selecting from a table where the column
name is dynamic.


 




The following code works:


 





style="font-size: 10pt; font-family: "Courier New"; color: black;"> 
 tmp_matrix

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">
dmt

style="font-size: 10pt; font-family: "Courier New"; color: navy;"><<

style="font-size: 10pt; font-family: "Courier New"; color: black;">
get rows where(

style="font-size: 10pt; font-family: "Courier New"; color: navy;">:

style="font-size: 10pt; font-family: "Courier New"; color: black;">Time
Period

style="font-size: 10pt; font-family: "Courier New"; color: navy;">==

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"Current"

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: black;">
style="">                             

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: navy;">&

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: black;">
name(

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: purple;">"Angioedema"

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: black;">)

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: black;">

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: navy;">==

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: black;">

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: teal;">1

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: black;">)

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: navy;">;



 




The following code does not work:



style="font-size: 10pt; font-family: "Courier New"; color: black;">

 





style="font-size: 10pt; font-family: "Courier New"; color: black;"> 
 one_dme

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"Angioedema"

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;"> 
 tmp_matrix

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">
dmt

style="font-size: 10pt; font-family: "Courier New"; color: navy;"><<

style="font-size: 10pt; font-family: "Courier New"; color: black;">
get rows where(

style="font-size: 10pt; font-family: "Courier New"; color: navy;">:

style="font-size: 10pt; font-family: "Courier New"; color: black;">Time
Period

style="font-size: 10pt; font-family: "Courier New"; color: navy;">==

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"Current"

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">                             

style="font-size: 10pt; font-family: "Courier New"; color: navy;">&

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">column

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">eval

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: black;">one_dme))

style="font-size: 10pt; font-family: "Courier New"; color: navy;">==

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: teal;">1

style="font-size: 10pt; font-family: "Courier New"; color: black;">)

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">

 




I could build an expression string and use eval(parse(string))
but I was wondering if there’s a
simpler way.


 




Thanks,


Peter



style="font-size: 10pt; font-family: "Courier New"; color: black;">

 

mpb

Super User

Joined:

Jun 23, 2011

I took the hint from the previous post and tried the following which seems to work (I simplified the condition):

one_dme = "Angioedema";
tmp_matrix = dmt << get rows where( As Column( Eval( one_dme ) ) == 1 );
pmroz

Super User

Joined:

Jun 23, 2011

Thanks mpb. Now if I could learn to read I wouldn't have posted my question!
I recently had some struggles with this issue and found that the only combination that worked for me was:

colExpr = Expr( Formula( As Column( Expr( inColumn ) ) ) );

Eval( Eval Expr( colExpr ) );

wait( 0 );

It all seems a bit clunky to me, but it was the only variant that I could get to work. I should also note that my As Column() statement was parsing another column from inside functions like Substitute(), Item(), and Word().