topic Re: How to increment based on empty rows in Discussions

How to increment based on empty rows

LargeElk892 — Wed, 13 Mar 2024 11:51:13 GMT

Hi,

I'd like to create a new column formula that increments (1, 2, 3....) only when empty rows are found (e.g. column 7 below).

Would you have a solution?

Thank you

Re: How to increment based on empty rows

jthi — Wed, 13 Mar 2024 12:02:25 GMT

Using formula with Lag() is most likely easiest option. Could you provide example with the result you want to have?

Re: How to increment based on empty rows

txnelson — Wed, 13 Mar 2024 13:29:33 GMT

Here is one way to handle your issue. Assuming that all of your columns are numeric, this will work

As Constant(
	count = 1;
	lagFlag = 0;
	dt = Current Data Table();
);
If( Is Missing( Sum( dt[Row(), 0] ) ) == 0,
	If( lagFlag == 1,
		count++;
		lagFlag = 0;
	)
,
	lagFlag = 1
);

If( lagFlag == 0, count );

Re: How to increment based on empty rows

LargeElk892 — Wed, 13 Mar 2024 21:51:37 GMT

Thank you very much Jim! I took me quite some time to go through each block to understand the logic but I finally got it :D