Multi-row within groups

[dreamlaurie]

I'm looking to find the correct ID within a group of zip codes.  The conditions are that the Name and Address should (closely) match and the Term Date should be null.  If there are Term Dates for all rows in that zip code then the result would simply be "Term."  See the highlighted groupings of zip codes and the correct ID (there could be several IDs but I'm just looking at the first Name and Address match going up the list).

[joshuaburkhow]

Hi @dreamlaurie 

There are going to be quite a large ways you can do this. Here is just one way! I like this because it's fairly scalable and clear which would be "term" and not. 

Hope this helps! If it does please mark this as solved so others can benefit 😉 

[dreamlaurie]

Thank you @joshuaburkhow!  I am able to see if an account needs to be terminated, however, the real data I'm after is the ID of the (or one of the) null accounts with a matching name and address.  My expected results would be :

121-4 for zip 01230 and

999-5 or 999-7 for zip 02215

These new IDs would replace the IDs expired on 10/1/20.

I've tried the multi-row expression below but did not get the expected results:

IF ([Name]=[Row-1:Name] and [Address]=[Row-1:Address] and [Term Date]="2020-01-10" and IsNull([Row-1:Term Date])) THEN [Row-1:ID] ELSE "Term" ENDIF