topic Re: call a column by referring to variable in Discussions
https://community.jmp.com/t5/Discussions/call-a-column-by-referring-to-variable/m-p/272402#M52979
<P><LI-USER uid="2687"></LI-USER> is quite right about this. The method I suggested was a lazy option and in hindsight not an approach I would recommend. If you just want a column with correct values you can do a calculation in a loop and not bother with a formula; if the formula is important to you then you need to check that your script has correctly encoded the formula - it's not sufficient for the numbers to appear correct. Script variables will not persist beyond the lifetime of the script, so the formula needs to correctly include column references - this is achieved by using expression-substitution techniques such as the one illustrated by Jim.</P>Fri, 12 Jun 2020 13:33:58 GMTDavid_Burnham2020-06-12T13:33:58Zcall a column by referring to variable
https://community.jmp.com/t5/Discussions/call-a-column-by-referring-to-variable/m-p/272339#M52967
<P>A complete newbie here,</P>
<P>could not find the solution in older posts,</P>
<P>I have created a variable containing an existing column name. I want to create a new column, were the data from two other columns is summed (calling the columns by variables). the usage of variable is for later use in a for loop.</P>
<P>Thanks for helping!</P>
<P> </P>
<P>for the below code:</P>
<P> </P>
<P> </P>
<PRE><CODE class=" language-jsl">dt = Current Data Table();
columnname1 = Column( dt, 1 ) << Get Name;
columnname2 = Column( dt, 2 ) << Get Name;
dt << New Column( "new_" || columnname1, Numeric , continuous, formula(column(dt, columnname1)+column(dt, columnname2)));</CODE></PRE>
<P> </P>
<P> </P>
<P>I get the following error:</P>
<P> </P>
<P>Column new_Column 1 2 Formula Interrupted<BR />Cannot convert argument to a number [or matrix] 1 times At rows: 2 Operation: Add, Column( dt, columnname1 ) + /*###*/Column( dt, columnname2 ) /*###*/<BR />Formula evaluation errors have been ignored</P>
<P>Column( "new_Column 1 2" )</P>
<P> </P>Fri, 12 Jun 2020 11:12:31 GMThttps://community.jmp.com/t5/Discussions/call-a-column-by-referring-to-variable/m-p/272339#M52967YBerger2020-06-12T11:12:31ZRe: call a column by referring to variable
https://community.jmp.com/t5/Discussions/call-a-column-by-referring-to-variable/m-p/272349#M52970
<P>You've already created variables that reference the columns. You can use those in your formula, but you need to use the AsColumn function:</P>
<P> </P>
<PRE><CODE class=" language-jsl">formula( AsColumn(columnname1) + AsColumn(columnname2) )</CODE></PRE>Fri, 12 Jun 2020 09:29:16 GMThttps://community.jmp.com/t5/Discussions/call-a-column-by-referring-to-variable/m-p/272349#M52970David_Burnham2020-06-12T09:29:16ZRe: call a column by referring to variable
https://community.jmp.com/t5/Discussions/call-a-column-by-referring-to-variable/m-p/272352#M52971
<P>You can get it to work, with</P>
<PRE><CODE class=" language-jsl">Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );
columnname1 = Column( dt, 4 ) << Get Name;
columnname2 = Column( dt, 5 ) << Get Name;
dt << New Column( "new_" || columnname1,
Numeric,
continuous,
formula(
As Column( dt, columnname1 ) + As Column( dt, columnname2 )
)
);</CODE></PRE>
<P>However, there is a potential problem with this. The stored formula is:</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="stored.PNG" style="width: 680px;"><img src="https://kvoqx44227.i.lithium.com/t5/image/serverpage/image-id/24560i04531CF3BD35462E/image-size/large?v=1.0&px=999" title="stored.PNG" alt="stored.PNG" /></span></P>
<P>This formula will continue to work, as long as your JMP session </P>
<OL>
<LI>Has the variables, dt, columnname1 and columnname2 defined. What happens if you save the data table, shutdown JMP, and then tomorrow you come in and startup JMP, and open the data table. The formula will not find those variables, and the formula will fail.</LI>
<LI>Within the session, the variables, dt, columnname1 and columnname2, do not change their value. So, if you create this column, and later on in your script, you happen to change the value of variable columnname1, since formula columns in a JMP data table are dynamic, the formula will rerun when changes to the data table take place, JMP will use the new value of the variable, and will create new values for the formula column.</LI>
</OL>
<P>What you really need to do, is to use a method that creates the actual JSL syntax for the formula. In the above example, what you really want is for the formula to be</P>
<PRE><CODE class=" language-jsl">:height + :weight</CODE></PRE>
<P>The JSL below is one way to do this. It uses the Substitute() function to implant the actual column names into the formula.</P>
<PRE><CODE class=" language-jsl">Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );
columnname1 = Column( dt, 4 ) << Get Name;
columnname2 = Column( dt, 5 ) << Get Name;
Eval(
Substitute(
Expr(
dt << New Column( "new_" || columnname1,
Numeric,
continuous,
formula( __c1__ + __c2__ )
)
),
Expr( __c1__ ), Parse( ":" || columnname1 ),
Expr( __c2__ ), Parse( ":" || columnname2 )
)
);</CODE></PRE>Fri, 12 Jun 2020 09:41:58 GMThttps://community.jmp.com/t5/Discussions/call-a-column-by-referring-to-variable/m-p/272352#M52971txnelson2020-06-12T09:41:58ZRe: call a column by referring to variable
https://community.jmp.com/t5/Discussions/call-a-column-by-referring-to-variable/m-p/272385#M52976
<P>thank you for your quick reply. it works!</P>Fri, 12 Jun 2020 12:30:24 GMThttps://community.jmp.com/t5/Discussions/call-a-column-by-referring-to-variable/m-p/272385#M52976YBerger2020-06-12T12:30:24ZRe: call a column by referring to variable
https://community.jmp.com/t5/Discussions/call-a-column-by-referring-to-variable/m-p/272402#M52979
<P><LI-USER uid="2687"></LI-USER> is quite right about this. The method I suggested was a lazy option and in hindsight not an approach I would recommend. If you just want a column with correct values you can do a calculation in a loop and not bother with a formula; if the formula is important to you then you need to check that your script has correctly encoded the formula - it's not sufficient for the numbers to appear correct. Script variables will not persist beyond the lifetime of the script, so the formula needs to correctly include column references - this is achieved by using expression-substitution techniques such as the one illustrated by Jim.</P>Fri, 12 Jun 2020 13:33:58 GMThttps://community.jmp.com/t5/Discussions/call-a-column-by-referring-to-variable/m-p/272402#M52979David_Burnham2020-06-12T13:33:58Z