topic Re: Help！ How should I set a formula or JSL to implement this calculation? in Discussions

Help！ How should I set a formula or JSL to implement this calculation?

AlphaColt356 — Tue, 21 Jan 2025 01:46:50 GMT

I want to fill "243" in row 12345 of column NO (column X, Raw5）， And push it downwards to fill (column 678910 of NO is filled with "1511")

Re: Help！ How should I set a formula or JSL to implement this calculation?

txnelson — Tue, 21 Jan 2025 02:50:41 GMT

Here are 2 solutions.

The first solution assumes that all of the data are in sets of 5 rows, so it always assigns values from every 5th row.

If( Mod( Row(), 5 ) == 0,
	Num( :X ),
	Num( :X[(Floor( Row() / 5 ) + 1) * 5] )
);

The second uses the value from the first NO row beyond or equal to the current row number.

As Constant(
	NORows = Current Data Table() << get rows where( :track no == "NO" )
);
val = Num( :X[NORows[1]] );
If( Row() == NORows[1],
	Try( NORows = NORows[Index( 2, Length( NORows ) )] )
);
val;

Re: Help！ How should I set a formula or JSL to implement this calculation?

AlphaColt356 — Tue, 21 Jan 2025 03:08:40 GMT

Thank you very much

Re: Help！ How should I set a formula or JSL to implement this calculation?

jthi — Tue, 21 Jan 2025 05:51:55 GMT

If you have to do this only once, you can also do this interactively in JMP (Note that JMP isn't able to capture all these steps done here, so it isn't that easy to automate based on JMP created script).

First create new column to keep the original order and sort your table descending by it

Next fix the X column values to either Numeric OR change NO to Character (I go with the X to numeric).

Then select one NO value from column track no, right click on it and select matching cells

Hold Alt and press on X column header to select X columns values which match NO value on track no

Press Ctrl + C (or otherwise copy) those values to clipboard, click on NO column header and paste them with Ctrl+V

Click on the NO column header to select all the values in it, right click on first value and fill

Sort your table again (ascending this time) with the column created in first step and delete the sorting column

And here is one more formula option using Lag (this assumes you want either characters or that you have numeric X column)

If(Mod(Row(), 5) == 0,
	:X
,
	Lag(:X, -1*(5 - Mod(Row(), 5)))
)