How to assign and increment an iteration count for multiple variables with unequal rows?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?
Advanced Statistical ModelingConsumer and Market ResearchRe: How to assign and increment an iteration count for multiple variables with unequal rows?Thanks for the recommendation. The data is sorted by SN and date which makes it easier.
Re: How to assign and increment an iteration count for multiple variables with unequal rows?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
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?@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?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?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
