246663_en-US

liliconcom

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!

 

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

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.

 

 

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

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=""></NEW>

 

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