how do I multiply each row values backward in one column until a criteria is met

Tinghui

Hello, I have the following challenge that I have trouble scripting with.

 

My starting data table looks like below. I would like to multiply Xi backwards starting from col2 =0 as the criteria and put the new values in a new column.

col1 col2

X1  0

X2  y2

X3  y3

…

Xn  yn

XX1 0

XX2 yy2

….

XXn yyn

….

….

My end data table would look like this:

col1 col2 col3

X1  0  Xn*Xn-1*……X3*X2*X1

X2  y2 Xn*Xn-1*…..X3*X2

X3  y3 Xn*Xn-1*….*X3

…      

Xn  yn  Xn

XX1 0   XXn*XXn-1*…..XX2*XX1

XX2 yy2 XXn*XXn-1*…..XX2

….

XXn yyn XXn

….

….

Can someone please help out? Very much appreciated!

txnelson

The calculations you want are very easy to do if the data are reversed, so here is a little script that will calculate the values using that idea

names default to here(1);

// create the sample table
dt = New Table( "example",
	Add Rows( 10 ),
	New Column( "col1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 2, 4, 5, 6, 7, 8, 9] ),
		Set Display Width( 48 )
	),
	New Column( "col2",
		Character( 1 ),
		"Nominal",
		Set Values( {"x", "x", "x", "y", "y", "y", "y", "z", "z", "z"} ),
		Set Display Width( 45 )
	)
);

// JSL to create the column you want
// create a new column to preserve the order of the data
dt << new column("origRow", formula(row()));
dt:origRow << delete formula;

// Reverse the order of the data
dt << sort(by(:origRow), order(descending), replace table(1));

// create the new column
dt << new column( "col3", formula(
	If( Row() == 1, x = :col1 );
If( Lag( :col2 ) != :col2,
	x = :col1,
	x = x * :col1
);
x;
));

// Since the formula is dependent on the order of the data, the formula needs
// to be removed and the values set to static values
dt:col3 << delete formula;

// reorder the data table back to the original order and delete the origRow column
dt << sort(by(:origRow), order(ascending), replace table(1));
dt << delete columns(:origRow);

And here is a piece of JSL that will create the new column without sorting

Names Default To Here( 1 );

// create the sample table
dt = New Table( "example",
	Add Rows( 10 ),
	New Column( "col1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 2, 4, 5, 6, 7, 8, 9] ),
		Set Display Width( 48 )
	),
	New Column( "col2",
		Character( 1 ),
		"Nominal",
		Set Values( {"x", "x", "x", "y", "y", "y", "y", "z", "z", "z"} ),
		Set Display Width( 45 )
	)
);


// create the new column
dt << New Column( "col3b" );

:col3b[N Rows( dt )] = :col1[N Rows( dt )];
For( i = N Rows( dt ) - 1, i >= 1, i--,
	If( :col2[i + 1] != :col2[i],
		:col3b[i] = :col1[i],
		:col3b[i] = :col3b[i + 1] * :col1[i]
	)
);