I have a large data set where the cycle changes over time. I would like to create a column that has an index by 1 at the start of each cycle and continues to count up until the next cycle starts then the counts starts back at 0. I have tried using the count and sequence functions shown below but I run into problems when my second cycle has more values that my first cycle (see diagram below where the cycle observation is the value I want the formula to produce and Column 109 is what is produced by the script.)
dt << New Column("Cycle Observation", Numeric, Formula (If(
:Cycle == 1, Sequence( 0, 1080, 1 ),
:Cycle == 2, Sequence( 0, 1439, 1 ),
:Cycle == 3, Sequence( 0, 360, 1 ),
:Cycle == 4, Sequence( 0, 360, 1 ),
:Cycle == 5, Sequence( 0, 360, 1 ),
:Cycle == 6, Sequence( 0, 180, 1 ),
:Cycle == 7, Sequence( 0, 180, 1 ),
:Cycle == 8, Sequence( 0, 180, 1 ),
:Cycle == 9, Sequence( 0, 180, 1 ),
:Cycle == 10, Sequence( 0, 180, 1 ),
Sequence( 0, 180, 1 )
)));
As @txnelson said there are lots of solutions in the community for similar problem.
You can use Lag(), but also Col formulas can be helpful in cases like these:
Col Cumulative Sum(1, :Cycle)
If you need to shift it by 1 you can add -1 to the formula after Col Cumulative Sum().
Not necessarily helpful in this particular case, but very helpful video by @brady_brady which will most likely be useful at some point:
Special Formula Columns
There are several previous Discussion entries concerning this topic, but since it is an easy solution, I have created another example for your specific situation.
Attached is a data table with a formula column that calculates the values desired
The formula is
If( Row() == 1,
value = 1,
If( :Cycle != Lag( :Cycle ),
value = 1,
value
++)
);
value;
While my solution works fine, @jthi solution below is a better solution.
