Multi-Row Formula Help

[burtre]

Hello, I am new to Alteryx and this is probably very simple for everyone; but I am having a challenging time trying to figure this out.

I have a list of data that I am comparing two rows to determine if criteria is met, if my criteria is met, I would like to add a "1" to the new field in both rows.  Here is an example:

if ([row-1:record id] = record id]) and ([row-1:date 1] = [date 1]) then "1" else "0" endif

Of course this logic, only places a "1" on the second record new field.  How do I also put a "1" in the new field of the first record, as well?

BEFORE

record id	date 1	new field
1234	5/3/2017
1234	5/4/2017
1235	5/5/2017
1235	5/5/2017
1236	5/6/2017
1236	5/6/2017

AFTER (WHAT I'M TRYING TO ACCOMPLISH)

record id	date 1	new field
1234	5/3/2017	0
1234	5/4/2017	0
1235	5/5/2017	1
1235	5/5/2017	1
1236	5/6/2017	1
1236	5/6/2017	1

Any assistance would be greatly appreciated.

Thanks

[NicoleJohnson · Accepted solution]

I think you'll need to include an OR statement (i.e. include double pipes || between your two statements) and look in both directions: row-1 and row+1. Additionally, I would check the box next to record ID in the Group By section so that you don't need to include that extra [record id] = [row-1: record id] statement.

Formula: IF [Row-1:date 1]=[date 1]||[date 1]=[Row+1:date 1] THEN 1 ELSE 0 ENDIF

Your configuration would look something like this:

Let me know if that works! :)

NJ

[BenMoss]

Of course this isn't the most efficient method but you could just add a second multi-row tool, with a condition like...

If [New Field] =1 then 1 elseif [row+1:New Field] then 1 else 0 endif

this will do the job.

Ben

[burtre]

I apologize for the late response, but thank you Nicole Johnson, your solution worked perfectly.