Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi-row formula

Nastya
8 - Asteroid

Hi everyone!

I have the following problem 

0 - remove, 1 - keep

NumberMarkOutput
2

0

0
210
210
221
221
221

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

 

Thanks!!

6 REPLIES 6
CharlieS
17 - Castor
17 - Castor

You can create the Output field using the following formula:

 

IF [Mark]==[Number] THEN 1
ELSE 0 ENDIF

 

I have attached a solution that shows how to implement this formula. 

ivoller
12 - Quasar

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.

Nastya
8 - Asteroid

Sorry! I've just understood that my example is not complete

NumberMarkOutput
2

0

0
210
210
221
221
221

 

NumberMarkOutput
1

0

0
111
111
121
121
121

 

So 1 after first match of "Number" and "Mark" the output for all next rows musk keep the same

jasperlch
12 - Quasar

Hi @Nastya

 

Not sure if I understand you correctly. Is below what you are after:

 

Capture1.PNG

Storm
9 - Comet

@Nastya:

 

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

 

 

Storm
9 - Comet

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.

Labels