topic Is there an alternative to iterating through rows using column formula? in Discussions

Is there an alternative to iterating through rows using column formula?

david_gillespie — Sat, 10 Jun 2023 23:37:46 GMT

I have a dataset which is a series of test runs, all collected in one table. The experiment is 6 sweeps from high to low speed for each sample, and what I need to do is assign the correct sweep number to each one (i.e. from 1 to 6) and then restart the sequence for the next sample. I could easily do this with the sequence or count function, however the number of points in each sweep is not always the same, it varies around 24-26. See image below for an example of one "sweep" from high to low speed before starting the next one.

I'm sure this could be done with a script, however my experience in that area is minimal. Is there a quick and dirty way of doing it using column formulas?

Re: Is there an alternative to iterating through rows using column formula?

jthi — Thu, 30 Sep 2021 14:08:50 GMT

Lag() could be helpful here.

Replace :Column 1 with your speed column:

If(Row() == 1, curVal = 1);
If(:Column 1 > Lag(:Column 1),
	curVal += 1
);
curVal;

Re: Is there an alternative to iterating through rows using column formula?

david_gillespie — Tue, 05 Oct 2021 08:07:55 GMT

Thanks for the help, that worked for each set of 6 runs of each sample. I added an extra line in to bring the counter back to 1 after each 6 sweeps so the below formula and code assigns the right sweep number with all the data stacked in one table!

If( Row() == 1, curVal = 1 );
If( :speed > Lag( :speed ),
	curVal += 1
);
If( curVal > 6, CurVal = 1 );
curVal;