topic Re: Scripting For loop to multiply values across row in Discussions

Scripting For loop to multiply values across row

Cdougz — Fri, 09 Jun 2023 22:08:29 GMT

Hi,

I am fairly new to scripting in JMP and have looked through many jmp forums for solutions but haven't been successful in modifying the solutions for my particular problem.

I have a script that produces a table of unknown number of columns and want to multiply each value in the row (there is only one row in the table generated) across the columns produced (column 1 * column 2 * column 3* etc). I simplified the table for the example, the actual table could have many more columns.

My loop multiplies each row by the next one but doesn't give me the final answer of all the rows multiplied by each other. I also just want the end product of all the values multplied together and don't need the product of each incremental value. Any help is very much appreciated.

New Table( "tsp",
	Add Rows( 1 ),
	New Column( "Row 1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [0.975] )
	),
	New Column( "Row 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [0.985] )
	),
	New Column( "Row 3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [0.983] )
	),
	New Column( "Row 4",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [0.839] )
	),
);

Ncol = N col(tsp);
For( i = 1, i <= Ncol, i++, 
	For( j = i + 1, j <= Ncol, j++, 
		RTP = Eval( Column( i ) << Get Name ) || " * " || Eval( Column( i+=1 ) << Get Name );
		New Column( RTP, Numeric , seteachvalue( Column( i )[] * Column( i+=1 )[]) );
	)
);

Re: Scripting For loop to multiply values across row

txnelson — Mon, 15 Mar 2021 21:49:11 GMT

You are working too hard. Below is what I believe you want

names default to here( 1 );
dt = New Table( "tsp",
	Add Rows( 1 ),
	New Column( "Row 1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [0.975] )
	),
	New Column( "Row 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [0.985] )
	),
	New Column( "Row 3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [0.983] )
	),
	New Column( "Row 4",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [0.839] )
	),

);

Ncol = N Col( dt ); // or NCol = N Col( data table("tsp"));
value = 1;
For( i = 1, i <= Ncol, i++,
	value = Column( dt, i )[1] * value
);
Show( value );

Re: Scripting For loop to multiply values across row

Cdougz — Mon, 15 Mar 2021 23:05:09 GMT

You sir, are absolutely wonderful! thank you so much it works perfect! I was definitely over complicating things haha. you are so helpful!

Re: Scripting For loop to multiply values across row

Mark_Bailey — Wed, 17 Mar 2021 16:24:50 GMT

All you need after New Table() is:

Product( i = 1, N Col( dt ), Column( dt, i )[1] );

Re: Scripting For loop to multiply values across row

Cdougz — Wed, 17 Mar 2021 19:38:34 GMT

I tried to put this into a For Each Row for situations where I would have mulitple rows to iterate through with this formula but it doesn't seem to be working and i'm not sure what my error is..I get the correct value in "RTP" for the first row "A" (86.9%) and then all the rest of the rows show the same value (75.5%) when they should all be different ...

New Table( "tsp",
	Add Rows( 5 ),
	New Column( "Site",
		Character,
		"Nominal",
		Set Selected,
		Set Values( {"A", "B", "C", "D", "E"} )
	),
	New Column( "Row 1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values(
			[0.974, 0.872, 0.927,
			0.974, 0.9033]
		)
	),
	New Column( "Row 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values(
			[0.968, 0.902, 0.931, .,
			0.962]
		)
	),
	New Column( "Row 3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values(
			[0.944, 0.968, 0.987, ., .]
		)
	),
	New Column( "Row 4",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [0.9847, 0.9471, ., ., .] )
	),
	New Column( "Row 5",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [0.990, 0.994, ., ., .] )
	)	
);

tsp << New Column("RTP", numeric, continuous, Format(Percent, 1));

for each row(:RTP= Product( i = 2, N Col( tsp), Column( tsp, i )[1] ));

Re: Scripting For loop to multiply values across row

Cdougz — Wed, 17 Mar 2021 20:48:07 GMT

nvm i found the error with the brackets at the end containing a 1 when it should have been empty, my code should have been

tsp << New Column("RTP", numeric, continuous, Format(Percent, 1));

for each row(:RTP= Product( i = 2, N Col( tsp), Column( tsp, i )[] ))