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

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

lwx228 — Mon, 10 Feb 2020 09:02:13 GMT

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.

lwx228 — Mon, 10 Feb 2020 09:08:47 GMT

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.

Mark_Bailey — Mon, 10 Feb 2020 11:14:56 GMT

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.

lwx228 — Mon, 10 Feb 2020 11:40:30 GMT

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