Need help in using multi row formula

[Naga]

Hello,

Need help. I need to count based on the 'field 2' segmentation. Every time when there is a F or G criteria change in field 2 then output should be in the following series. I tried using multi row formula with if condition. However, my requirement not met because of string conversion error.

Record Number	Field 1	Field 2	Output
1	A	F	1
2	B	F	2
3	C	F	3
4	D	F	4
5	E	F	5
6	A	G	1
7	B	G	2
8	C	G	3
9	D	G	4
10	A	G	1
11	B	G	2
12	C	G	3
13	D	G	4
14	A	F	1
15	B	F	2
16	C	F	3
17	D	F	4
18	E	F	5
19	A	G	1
20	B	G	2
21	C	G	3
22	D	G	4

[DavidThorpe · Accepted solution]

Hi @Naga 

The following formula should work:

IF [Field 2]!=[Row-1:Field 2] THEN 1 ELSE
[Row-1:New Field] +1 ENDIF

See the attached example

Thanks!

[DavidThorpe · Accepted solution]

Just noticed your example resets the count when there is an 'A' in Field 1 also, in that case the following formula is appropriate:

IF [Field 2]!=[Row-1:Field 2] THEN 1
ELSEIF [Field 1] = 'A' THEN 1
ELSE [Row-1:New Field] +1 ENDIF

[Naga]

Thanks..it is pretty swift response... 🙂

[Naga]

Yep...I was about to respond on this. This helps.

Also I need  standard segmentation of numbering the rows. Like segment 1st 10 rows 1 to 10 and again loop 1 to 10 for next until end. There is no dependency on the other fields.

[DavidThorpe · Accepted solution]

You can use a regular formula tool for this, using the following:

CEIL([Record Number]/10)

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Increment-by-1-Every-15-Rows/td-p/3066...

Thanks!