cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
lwx228
Level VIII

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

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

View solution in original post

3 REPLIES 3
lwx228
Level VIII

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?

 

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 );
lwx228
Level VIII

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