topic Re: Cycle Problem in Discussions

Cycle Problem

scott1588 — Thu, 25 Jul 2024 02:33:27 GMT

I have this recurring cycle detected problem that I cannot figure out and I'm hoping someone can help me.

See the attached example script. It generates a table. The idea here is I want to label each continuous runs of 1 in Column 1 as an Event with a sequential number.

The formula works. But when I make any modifications to the table or Rerun Formulas I get one, two, or sometimes three cycle detected errors. This is just an example table. On my actual data table, I have had to hit Ignore All Cycles sometimes up to a dozen times if I change a column formula or do other editing on the table.

Does anyone know what is causing this?

Thanks

New Table( "Cycle Problem",
	Add Rows( 21 ),
	New Table Variable( "P1", 3 ),
	New Column( "Column 1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[0, 0, 0, 1, 1, 1, 1, 1, 0, 0, 1, 1, 0, 0, 0, 1, 1, 1, 1, 0, 0]
		)
	),
	New Column( "Column 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			If(
				Row() == 1, :P1 = 0,
				:Column 1 == 1 & :Column 1[Row() - 1] == 0, :P1 = :P1 + 1,
				:Column 1 == 1, :P1
			)
		),
		Set Selected
	)
)

Re: Cycle Problem

peng_liu — Thu, 25 Jul 2024 03:30:13 GMT

Not sure what the cycle warning means. Looks like a false alarm to me. But meanwhile, I guess the result of following script is what you try to achieve and it does not have the cycle issue.

New Table( "Cycle Problem",
	Add Rows( 21 ),
	New Column( "Column 1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[0, 0, 0, 1, 1, 1, 1, 1, 0, 0, 1, 1, 0, 0, 0, 1, 1, 1, 1, 0, 0]
		)
	),
	New Column( "Column 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			If(
				Row() == 1, 0,
				:Column 1 == 1 & :Column 1[Row() - 1] == 0, :Column 2[Row() - 1] + 1,
				:Column 2[Row() - 1]
			)
		),
		Set Selected
	)
)

Re: Cycle Problem

jthi — Thu, 25 Jul 2024 04:46:58 GMT

Most likely the cycle problems is caused by you setting :P1 table variable value and comparing them in same formula

If(Row() == 1, 
	:P1 = 0
, :Column 1 == 1 & :Column 1[Row() - 1] == 0, 
	:P1 = :P1 + 1
, :Column 1 == 1, 
	:P1
)

You can use formula like

If(Row() == 1, 
	retval = 0
, :Column 1 == 1 & Lag(:Column 1) == 0, 
	retval = retval + 1
);
retval;

Lag() gives you access to earlier values in a column

Re: Cycle Problem

brady_brady — Thu, 25 Jul 2024 12:07:33 GMT

Hi,

It is also OK for you to lag the formula column itself. That is, you could use this formula in column C2 for the values you have in C1:

If( Row() == 1,
	0,
	Lag( :C2 ) + (Lag( :C1 ) == 0 & :C1 == 1)
)

Re: Cycle Problem

scott1588 — Thu, 25 Jul 2024 13:23:54 GMT

@peng_liu , @jthi , and @brady_brady ...

Thanks for you assistance on this. The scripts you provided, however, did not quite do what I need to do and when I tried to modify them they didn't work. Specifically, The numbering in Column 2 should only appear when there is a 1 in Column 1. If there is a 0 in Column 1, then Column 2 is blank. Like this...

When I try to add the additional IF statement to accomplish this, the scripts fail.

For example, here is what I did with @peng_liu script.

New Table( "Cycle Problem",
	Add Rows( 21 ),
	New Column( "Column 1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[0, 0, 0, 1, 1, 1, 1, 1, 0, 0, 1, 1, 0, 0, 0, 1, 1, 1, 1, 0, 0]
		)
	),
	New Column( "Column 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			If(
				Row() == 1, 0,
				:Column 1 == 1 & :Column 1[Row() - 1] == 0,
					:Column 2[Row() - 1] + 1,
				:Column 1 == 1, :Column 2[Row() - 1]
			)
		),
		Set Selected
	)
)

I also noticed another problem that may be related. When I run the original script I provided, Column 2 is blank. However, if I add a Column 3 with any kind of random formula, suddenly the values appear in Column 2. This happens on some of my other tables as well.

@brady_brady I am still working on your solution to see if I can get it to work.

In the meantime, is there any further guidance you can provide to help me out here.

Thanks much for all your help.

Re: Cycle Problem

jthi — Thu, 25 Jul 2024 13:44:25 GMT

You can for example add one If statement to the end

Names Default To Here(1);

dt = New Table("Cycle Problem",
	Add Rows(21),
	New Column("Column 1",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([0, 0, 0, 1, 1, 1, 1, 1, 0, 0, 1, 1, 0, 0, 0, 1, 1, 1, 1, 0, 0])
	)
);

dt << new column("C", Numeric, Ordinal, Formula(
	If(Row() == 1,
		retval = 0;
	, :Column 1 == 1 & Lag(:Column 1) == 0,
		retval = retval + 1
	);
	If(:Column 1 == 1,
		retval;
	,
		.
	);
));

Re: Cycle Problem

brady_brady — Thu, 25 Jul 2024 13:46:42 GMT

This seems to work but is cumbersome.

If( Row() == 1,
	0,
	If( :C1 == 1,
		If( Lag( :C1 ) == 0,
			Max( :C2[Index( 1, Row() - 1 )] ) + 1,
			Lag( :C2 )
		),
		.
	)
)

Another option, a formula for column "C3":

If(
	Row() == 1,
		c = 0;
		c;
	,
		:C1 == 0, 
		.
	,
		Lag( :C1 ) == 1, 
		Lag( :c3 )
	,
		c++;
		c;
)

Re: Cycle Problem

scott1588 — Thu, 25 Jul 2024 13:51:28 GMT

@jthi ! That seems to work. Although I'm not sure I understand why it works with a separate IF statement and it doesn't work with a combined IF statement as in my @peng_liu modification.

But more importantly, why does creating the table variable cause a problem while creating a variable within the script does not?

Re: Cycle Problem

jthi — Thu, 25 Jul 2024 14:02:30 GMT

Also Col Cumulative Sum() can be used for this

Names Default To Here(1);

dt = New Table("Cycle Problem",
	Add Rows(21),
	New Column("Column 1",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([0, 0, 0, 1, 1, 1, 1, 1, 0, 0, 1, 1, 0, 0, 0, 1, 1, 1, 1, 0, 0])
	)
);

dt << new column("C", Numeric, Ordinal, Formula(
	If(:Column 1,
		Col Cumulative Sum(Dif(:Column 1) == 1)
	,
		.
	);
));

Re: Cycle Problem

brady_brady — Thu, 25 Jul 2024 14:44:15 GMT

That's a great one.

Re: Cycle Problem

scott1588 — Thu, 25 Jul 2024 15:24:58 GMT

@peng_liu , @jthi , and @brady_brady Thank you all for your solutions. I particularly like @jthi 's solution. I never knew Col Cumulative Sum could be used that way. Very simple, efficient, and elegant.

I'm still trying to wrap my head around how variable work in JMP. I'm not quite sure I have the whole picture yet but I'm working on it.

Re: Cycle Problem

jthi — Thu, 25 Jul 2024 15:53:28 GMT

It can but I'm not 100% sure if you should even though it is one of the better features of JMP Formulas (JMP support told me it is unintended behavior). Here is a wish list item hoping that JMP would acknowledge that it is definitely needed behavior .

For the cycle problem and explanation of it, you could contact JMP Support. I could only provide guesses (which might be correct or totally incorrect) as I'm not a JMP developer but I can kinda understand it as it tries to be multiple values at the same time. This might also be a good read