topic Re: How to assign and increment an iteration count for multiple variables with unequal rows? in Discussions

How to assign and increment an iteration count for multiple variables with unequal rows?

sumanthcb141977 — Sat, 10 Jun 2023 23:58:04 GMT

Hello all.

Im trying to create a column which assigns an iteration number (numeric and continuous) based on column "Comment = End of Cycle" and increments it for the same SN. The number of data points for each SN is not the same.

For example.

for SN = "A", rows 1-9: Iteration =1, rows 10-16 Iteration =2

for SN = "B", rows 17-27: Iteration =1, rows 28-36 Iteration =2 and so on...

Tried a few variations using the Row ( ) function but was not successful. Can someone provide some guidance on this?

Re: How to assign and increment an iteration count for multiple variables with unequal rows?

ron_horne — Fri, 09 Dec 2022 18:56:41 GMT

Hi @sumanthcb141977
please try the following.

New Table( "Untitled",
	Add Rows( 36 ),
	New Column( "SN",
		Character,
		"Nominal",
		Set Values(
			{"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A",
			"A", "A", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B",
			"B", "B", "B", "B", "B", "B", "B", "B"}
		)
	),
	New Column( "iteration",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1,
			1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2]
		)
	),
	New Column( "iteration count",
		Numeric,
		"Nominal",
		Format( "Best", 9 ),
		Formula( Col Cumulative Sum( 1, :SN, :iteration ) )
	)
)

If this is not what you are looking for please provide the data table so it is much easier to address.
let us know if it worked

Re: How to assign and increment an iteration count for multiple variables with unequal rows?

sumanthcb141977 — Fri, 09 Dec 2022 19:09:03 GMT

Thanks for the response. Sorry had to be more specific. I dont need an additional column by the name of iteration count. I wanted the formula for the iteration to assign a number and increment it based on the "end of cycle" comment. Below is the image of what i wanted the iteration column to look like.

Im attaching the jmp file per the request.

Re: How to assign and increment an iteration count for multiple variables with unequal rows?

ron_horne — Fri, 09 Dec 2022 20:02:21 GMT

@sumanthcb141977

try the following. it is not robust and requires the data to be sorted by SN and then date.

New Table( "Untitled",
	Add Rows( 36 ),
	New Column( "SN",
		Character,
		"Nominal",
		Set Values(
			{"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A",
			"A", "A", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B",
			"B", "B", "B", "B", "B", "B", "B", "B"}
		)
	),
	New Column( "Comment",
		Character,
		"Nominal",
		Set Values(
			{"Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle",
			"End of Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle",
			"End of Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle",
			"Cycle", "Cycle", "Cycle", "Cycle", "End of Cycle", "Cycle", "Cycle",
			"Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle", "Cycle"}
		)
	),
	New Column( "iteration count",
		Numeric,
		"Nominal",
		Format( "Best", 9 ),
		Formula(
			If(
				Row() == 1, 1,
				Lag( :SN ) == :SN & Lag( :Comment ) == "Cycle",
					Lag( :iteration count ),
				Lag( :SN ) == :SN & Lag( :Comment ) == "End of Cycle",
					Lag( :iteration count ) + 1,
				Lag( :SN ) != :SN & Lag( :Comment ) == "End of Cycle", 1
			)
		),
		Set Selected
	)
)

perhaps others have a more robust way of indexing this.

Re: How to assign and increment an iteration count for multiple variables with unequal rows?

ron_horne — Sat, 10 Dec 2022 11:33:39 GMT

@sumanthcb141977

my only other idea would be to use an additional table. perhaps use new table as is an use its value as place markers. this will allow you to have one table or numeric values and another with categories. this will reduce the hard conversions between numbers and categories.

Re: How to assign and increment an iteration count for multiple variables with unequal rows?

jthi — Mon, 12 Dec 2022 06:53:09 GMT

Here is other option for a formula

Col Cumulative Sum(
	Col Min(Row(), :SN) == Row() | Lag(:Comment) == "End of Cycle",
	:SN
);

Re: How to assign and increment an iteration count for multiple variables with unequal rows?

sumanthcb141977 — Mon, 12 Dec 2022 15:00:50 GMT

Thanks for the recommendation. The data is sorted by SN and date which makes it easier.