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

[#1]

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!

[#2]

I don't understand your 3 sets of columns.  It appears as if you are changing column names in the middle of a data table, which will not work in JMP.  Is this table format coming from an Excel spreadsheet?

Here is a simple formula that will do what you want for a data table that looks like 

I have attached the data table.

The formula used is:

x = :col2[Row()];
If( Row() < N Rows( Current Data Table() ),
	For( i = Row() + 1, i <= N Rows( Current Data Table() ), i++,
		x = x * :col2[i]
	)
);
x;

This same idea can be used if there are multiple places in a data table that need to restart the multiplying.  The identification of when to restart and when to end would just have to be worked out

[#3]

I removed my post since I missed the grouping variable. My approach would not work in this scenario.

[#4]

Hi Jim @txnelson 

Thanks for your reply.

No, my column names do not change. They stay as col1, col2 as given. I would like to add a new column (col3) that does the backward multiplication for a same value given in col2. I'm demonstrating my need in the following table. Could you please help? 

col1 col2 col3

1     x     6 (i.e. 1 x 2 x 3)

2     x     6 (i.e. 2 x 3)

3     x     3 (i.e. 3)

2     y     240 (i.e. 2x4x5x6)

4     y     120 (i.e. 4 x 5 x6)

5     y     30 (i.e. 5 x6)

6     y     6 (i.e. 6)

7     z     504 (i.e. 7x8x9)

8     z     72 (i.e. 8x9)

9     z     9

Thanks!

[#5]

Here is the new formula......it is the same method as the last formula, but just having to find the start and end point for each of the groups.

If( Row() == 1,
	curCol = :col2;
	end = Max( Current Data Table() << get rows where( :col2 == curCol ) );
	x = :col1;
,
	:col2 != curCol;
	curCol = :col2;
	end = Max( Current Data Table() << get rows where( :col2 == curCol ) );
	x = :col1;
);
For( i = Row() + 1, i <= end, i++,
	x = x * :col1[i]
);
x;

[#6]

---

Hi Jim @txnelson 

That actually worked on a smaller table (i.e. several thousands of rows). Thank you.

The 'new' issue that I'm having is that my data table is very large. As I embedded the formula into the new column and click on 'apply', the program got hung and I had to kill jmp to get out.

As I hover over the formula, here is the warning message regarding 'current data table'.

"Current data table() has side effects and is usually inappropriate to be used inside a formula. If this is necessary, you might use expr or eval to prevent execution in formulas....."

My scripting skills are very limited. Thanks for your further help on this subject!

Regards,

Tinghui

@txnelson wrote:

Here is the new formula......it is the same method as the last formula, but just having to find the start and end point for each of the groups.

If( Row() == 1,
	curCol = :col2;
	end = Max( Current Data Table() << get rows where( :col2 == curCol ) );
	x = :col1;
,
	:col2 != curCol;
	curCol = :col2;
	end = Max( Current Data Table() << get rows where( :col2 == curCol ) );
	x = :col1;
);
For( i = Row() + 1, i <= end, i++,
	x = x * :col1[i]
);
x;

---

[#7 · Accepted solution]

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

[#8]

@txnelson

Thanks Jim! I really wish I were a scripting genius like yourself. It works great!