Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
lwx228
Level VII

How to use the formula row by row to find the dynamic change of the total number of columns.

How to use the formula row by row to find the dynamic change of the total number of columns.
Such as setting:
The number a is the number of starting column positions that need to be summed
The number b is the total number of columns that need to be summed

dt = Open( "$SAMPLE_DATA/Diabetes.jmp" );
dt << New Column( "test", formula( As Column( 6 ) + As Column( 7 ) + As Column( 8 ) + As Column( 9 ) ) );
dt << run formulas;
Column( "test" ) << deleteFormula;

Such as:
A = 6;
B = 4;
Find the sum of columns 6, 7, 8, and 9 in the new "text" column (starting with column a, total column b).

Thanks!

2020-02-10_17-00.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: How to use the formula row by row to find the dynamic change of the total number of columns.

This situation is weird. This script builds the formula:

 

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/Diabetes.jmp" );

col = dt << New Column( "test" );

lo = 6;
hi = 9;
f = Expr( Add() );

For( c = lo, c <= hi, c++,
	r = (dt << Get Column Reference( Eval List( { Column( dt, c ) << Get Name } ) ))[1];
	Insert Into( f, r );
);

Eval(
	Substitute(
		Expr( col << Set Formula( fff ) ),
		Expr( fff ),
		Name Expr( f )
	)
);

But it does not evaluate. Yet it works if I examine the preview in the formula editor.

 

Capture.JPG

 

Anyway, why use a formula when you don't want to?

 

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/Diabetes.jmp" );

col = dt << New Column( "test" );

lo = 6;
hi = 9;
sum = J( N Row( dt ), 1, 0 );

For( c = lo, c <= hi, c++,
	sum += Column( dt, c ) << Get As Matrix;
);

col << Set Values( sum );
Learn it once, use it forever!

View solution in original post

3 REPLIES 3
Highlighted
lwx228
Level VII

Re: How to use the formula row by row to find the dynamic change of the total number of columns.

  • I tried to write it this way, but it didn't work.

 

a=6;b=4;
dt = Current Data Table();
dt<<New Column("text",formula(
g=0;
for(i=6,i<=a+b-1,i++,
g=g+As Column(i+1)	
)));dt<<run formulas;
Column("text")<<deleteFormula;Wait(0);

 

Looks like it's going to be in the brute force form of a line by line loop, right?

 

Highlighted

Re: How to use the formula row by row to find the dynamic change of the total number of columns.

This situation is weird. This script builds the formula:

 

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/Diabetes.jmp" );

col = dt << New Column( "test" );

lo = 6;
hi = 9;
f = Expr( Add() );

For( c = lo, c <= hi, c++,
	r = (dt << Get Column Reference( Eval List( { Column( dt, c ) << Get Name } ) ))[1];
	Insert Into( f, r );
);

Eval(
	Substitute(
		Expr( col << Set Formula( fff ) ),
		Expr( fff ),
		Name Expr( f )
	)
);

But it does not evaluate. Yet it works if I examine the preview in the formula editor.

 

Capture.JPG

 

Anyway, why use a formula when you don't want to?

 

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/Diabetes.jmp" );

col = dt << New Column( "test" );

lo = 6;
hi = 9;
sum = J( N Row( dt ), 1, 0 );

For( c = lo, c <= hi, c++,
	sum += Column( dt, c ) << Get As Matrix;
);

col << Set Values( sum );
Learn it once, use it forever!

View solution in original post

Highlighted
lwx228
Level VII

Re: How to use the formula row by row to find the dynamic change of the total number of columns.

Thank markbailey!

https://community.jmp.com/t5/Discussions/How-to-SUM-rows-with-variable-column-names/m-p/9817 

 

Seeing this post, I also tried this method, but to determine the number of text format columns.

dt = Open( "$SAMPLE_DATA/Diabetes.jmp" );
a = 4;//has 2 text format columns.
b = 4;
m = (dt << get as matrix)[0, Index( a, a + b - 1 )];
s = (V Sum( m` ))`;
dt << New Column( "test", Numeric, Continuous, Values( s ) );
Article Labels

    There are no labels assigned to this post.