Identify and flag three consecutive negative or positive values

[John2275]

Hi all, 

I would like to create a workflow that identifies three consecutive negative or positive values across rows in a column, and then flag whether it is a negative (N) or positive (P) sequence in a separate column in the row directly after the sequence.

Furthermore, there shouldn't be any consecutive flags of the same type. This means that when a negative sequence is found, the next sequence marked can only be positive, and likewise for a positive sequence (the next flag can only be negative, even if there is another positive sequence).

RecordID	Value	Flag
1	-234.00
2	123.00
3	43.00
4	25.00
5	-235.00	P
6	-22.00
7	-356.00
8	-3524.00	N
9	-45.00
10	-654.00
11	-345.00
12	7543.00
13	57.00
14	56.00
15	9534.00	P
16	-736.00
17	426.00
18	-65.00

I was thinking of using a multi-row formula with nested if statements, but I can't seem to get the "no consecutive flags of the same type" part right. Any and all help is appreciated, thank you.

[Adrian_T]

Hey @John2275 

Here's one way of doing this. I used a Multi-Row Formula Tool to determine the first part of your logic to identify P and N. To factor in "no consecutive flags of the same type", I filtered out the rows with P and N flags, used another Multi-Row Formula Tool to remove consecutive flags of the same type before joining them back to the main data.

Sample workflow attached, hope this helps! 🙂

[flying008 · Accepted solution]

Hi,@John2275 

please look that , 

Input			Output
RecordID	Value		Value	Flag
1	-234		-234
2	123		123
3	43		43
4	25		25
5	-235		-235	P
6	-22		-22
7	-356		-356
8	-3524		-3524	N
9	-45		-45
10	-654		-654
11	-345		-345
12	7543		7543
13	57		57
14	56		56
15	9534		9534	P
16	-736		-736
17	426		426
18	-65		-65

********************

1- Multi-Row Formula:  Create New Field [Tmp]    --->  Define the same token on 3 consecutive Rows from field [Value]

iif((Min([Row-1:Value], [Row-2:Value], [Row-3:Value]) >0 or Max([Row-1:Value], [Row-2:Value], [Row-3:Value]) <0) && isempty([Row-1:Tmp]) && isempty([Row-2:Tmp]),iif([Row-1:Value]>0,'P','N'),Null())

2- Multi-Row Formula:  Update Existing Field [Tmp]    --->  Fill down the column for distinguish identical rows.

iif(isempty([Tmp]) && !isempty([Row-1:Tmp]),[Row-1:Tmp],[Tmp])

3- Multi-Row Formula:  Create New Field [Flag]    --->  Cleansing useless rows to rebuilding final mark from field [Tmp]

iif([Tmp]=[Row-1:Tmp] or [Tmp]=[Row-2:Tmp],Null(),[Tmp])

4- Remove the field [Tmp] .