Cycle Problem

[#1]

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
	)
)

[#2]

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
	)
)

[#3]

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

[#4]

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)
)

[#5]

@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.

[#6]

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;
	,
		.
	);
));

[#7]

@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?

[#8 · Accepted solution]

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)
	,
		.
	);
));

[#9]

That's a great one.

[#10]

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;
)