Identify and flag three consecutive negative or positive values

Question

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).

RecordIDValueFlag1-234.00 2123.00 343.00 425.00 5-235.00P6-22.00 7-356.00 8-3524.00N9-45.00 10-654.00 11-345.00 127543.00 1357.00 1456.00 159534.00P16-736.00 17426.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.

flying008 · Accepted Answer

Hi,@John2275

please look that ,

Input  Output RecordIDValue ValueFlag1-234 -234 2123 123 343 43 425 25 5-235 -235P6-22 -22 7-356 -356 8-3524 -3524N9-45 -45 10-654 -654 11-345 -345 127543 7543 1357 57 1456 56 159534 9534P16-736 -736 17426 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] .