Multi-row formula
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!!
Solved! Go to Solution.
- Labels:
- Expression
- Help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Nastya
Do you actually want to remove the rows or just set the Output to 0 or 1?
To create the column use standard Formula tool, to remove add a Filter tool.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sorry! I've just understood that my example is not complete
Number | Mark | Output |
2 | 0 | 0 |
2 | 1 | 0 |
2 | 1 | 0 |
2 | 2 | 1 |
2 | 2 | 1 |
2 | 2 | 1 |
Number | Mark | Output |
1 | 0 | 0 |
1 | 1 | 1 |
1 | 1 | 1 |
1 | 2 | 1 |
1 | 2 | 1 |
1 | 2 | 1 |
So 1 after first match of "Number" and "Mark" the output for all next rows musk keep the same
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
See also if the attached will work.
Assigns a record id, which is used for two purposes:
1 - Identify the first occurrence of a given "Number"
2 - Later resort the data back to its original order (since we will sort within the workflow)
Then summarize tool gets the earliest (minimum) record ID per each Number. This would be the first time that Number appears in the data - which will be used to assign the Output for all instances of that Number.
This information is then joined back to the original data (with record ID included). Basically, column "first_occurrence" is set to Y if that record is the first appearance of that Number; else it's set to N.
Then the data is sorted by number (ascending - order doesn't matter) and first_occurrence (descending - order DOES matter. This puts the Number's first appearance first for that Number.)
Then the multi-row function creates the output field 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: it's the Number's first appearance and it matches Mark (assign 1); it's the Number's first appearance and it DOESN'T match Mark (assign 0); or it's not the Number's first appearance (assign the Output from the Number's first appearance).
Then we re-sort by record ID to get data back in same order as input; and a select tool scopes it down to just the original fields (plus the Output).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.