284377_en-US

liliconcom

how to add new column in a row with a for loop

i want to add series new column with a formula B2/A2,C2/A2...like below example

how to i implement it

Thanks

 

 

 

 

 

colname = dt << get column names( string, continuous );

nc = N Items( colname );
For( p = 1, p < nc, p++,
    For( i = 1, i < nc, i++,
        a = "Bin" || Char( p );
        b = Char( a ) || Char( "(%)" );
        If( Contains( colname[i], a ),
            dt << New Column( b, formula( :colname[i] / :name( "Total" ) ) )
        );
    )
);

 

ScriptingRe: how to add new column in a row with a for loop

In case there are special characters in the column name you could use Parse( "As Column \!"" || colname[i] || "\!"" ) in the substitution.  The original post tried to use Name(), which is hard to use in scripts as it only accepts a quoted string.

Re: how to add new column in a row with a for loop

Nice. I like the mix'n'match of expression manipulation and character manipulation at the end!

1  Eval(
2   Substitute(
3    Expr(
4     dt << New Column( colname[i] || "%",format("Percent",7,2),
5      formula( __col__ / :name( "Total" ) ) )
6    ),
7    Expr( __col__ ), 
8    Parse( ":" || colname[i] )
9   )
10  )

</CODE>Lines 4 and 5 are the JSL that is manipulated as an expression; the __col__ is a placeholder.</P><P>Lines 3 and 6 are <EM>quoting</EM> the expression so it doesn't try to run.</P><P>Line 7 identifies the bit to be replaced, also <EM>quoted</EM> so it doesn't execute.</P><P>Line 8 is the replacement value. This is interesting because it actually builds some JSL as a concatenation of two strings, then parses it into an expression. The result of the parse function is an expression.</P><P>Lines 2 and 9 are the <EM>Substitute(source,pattern,replacement)</EM> function. It returns an expression built from the three expression arguments.</P><P>Lines 1 and 10 evaluate the expression.</P></LINGO-BODY><LINGO-SUB id="lingo-sub-284389" slang="en-US">Re: how to add new column in a row with a for loop</LINGO-SUB><LINGO-BODY id="lingo-body-284389" slang="en-US"><P>You were making the issue more complex than it needs to be.&nbsp; Below is a rework of your code, with a bit of annotation</P> <PRE><CODE class=" language-jsl">Names Default To Here( 1 ); // This line tells JMP to isolate the variable used to this script // Set a variable to point to the current active data table dt = Current Data Table(); // Get the column names colname = dt << get column names( string, continuous ); // Determine the number of columns to loop through nc = N Items( colname ); // Loop across all of the columns in the colname list // Your original code was For( i = 1, i < nc, i++, // with "i < nc" the last colname would not be processed For( i = 1, i <= nc, i++, // If this is a column name that starts with "Bin" // a new % column needs to be added If( Left( colname[i], 3 ) == "Bin", // Create the new column Eval( Substitute( Expr( dt << New Column( colname[i] || "%",format("Percent",7,2), formula( __col__ / :name( "Total" ) ) ) ), Expr( __col__ ), Parse( ":" || colname[i] ) ) ) ) );