Multi-row formula

Nastya

Hi everyone!

I have the following problem 

0 - remove, 1 - keep

Number	Mark	Output
2	0	0
2	1	0
2	1	0
2	2	1
2	2	1
2	2	1

I need to create the column "Output". If number = mark => keep, if number doesn't equal to output => remove

Thanks!!

Storm

Or,@nastya, take look at attached workflow, see if it does the job.

Assigns record ID which is used in two ways:
1, Identify first appearance per Number (later used to assign Output)
2, Re-sort data at end back to its original order (since we will sort within workflow)

Summarize tool then gets first appearance per each Number.

This is then joined back to the original data (with record ID included); which then populates a field called "first_occurrence" with Y if that's the first appearance of that Number, but otherwise populates with N.

Then the data is sorted by Number (ascending/descending doesn't matter) and "first_occurrence" (descending - DOES matter, because this puts the first record per Number at the top of results for that Number).

Multi-field tool then assigns Output with this formula:

if [first_occurrence] = 'Y' and [number] = [mark] then '1'
elseif [first_occurrence] = 'Y' and [number] != [mark] then '0'
else[row-1:output]
endif

There are only 3 possibilities per record here: It's the Number's first appearance and Number equals Mark (output 1); It's the Number's first appearance and Number does NOT equal Mark (output 0); it isn't the Number's first appearance (take the output from the Number's first appearance).

Then we re-sort data to its original order; and a select tool scopes it down to only the incoming fields plus output field.

Community_wf_20180817_assigning_output_to_first_number_occurrence.yxmd