Choose Language Hide Translation Bar
Highlighted
alexching
Level I

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

 

 

 

 

alexching_0-1596703060108.png

 

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" ) ) )
        );
    )
);

 

3 REPLIES 3
Highlighted
txnelson
Super User

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

You were making the issue more complex than it needs to be.  Below is a rework of your code, with a bit of annotation

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] )
			)
		)
	)
);
Jim
Highlighted
Craige_Hales
Staff (Retired)

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		)

Lines 4 and 5 are the JSL that is manipulated as an expression; the __col__ is a placeholder.

Lines 3 and 6 are quoting the expression so it doesn't try to run.

Line 7 identifies the bit to be replaced, also quoted so it doesn't execute.

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.

Lines 2 and 9 are the Substitute(source,pattern,replacement) function. It returns an expression built from the three expression arguments.

Lines 1 and 10 evaluate the expression.

Craige
Highlighted
ih
ih
Level VII

Re: 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.

Article Labels