I need to be able to create a Field that flags either yes or no based on a particular set of criteria. Please see mock table below.
| RefNoType | RefSeq | Claim Type State | Claim Type State Done Date | Claim Type State Id Changed Flag |
| 539902AD | 1 | Notified | 08/11/2011 10:40:44 | No |
| 539902AD | 1 | Closed | 08/11/2011 10:40:44 | Yes |
| 539902PI | 1 | Closed | 08/11/2011 10:40:44 | No |
| 539902PI | 1 | Notified | 08/11/2011 10:40:44 | No |
| 539902PI | 4 | ReOpened | 13/12/2011 10:10:42 | No |
| 539902PI | 10 | ReClosed | 18/04/2012 15:30:51 | No |
| 539902PI | 11 | ReOpened | 28/06/2012 09:29:28 | No |
| 539902PI | 12 | ReClosed | 29/06/2012 08:43:33 | Yes |
| 539902TP | 1 | Notified | 08/11/2011 10:40:44 | No |
| 539902TP | 2 | Closed | 08/11/2011 11:06:14 | No |
| 539902TP | 7 | ReOpened | 05/01/2012 15:16:36 | No |
| 539902TP | 8 | ReClosed | 06/01/2012 13:06:34 | Yes |
I am trying to create the field highlighted with the red writing.
I need to distinguish when the last RefSeq of a particular RefNoType is either Closed or ReClosed when this is true to show 'Yes'
if it is notified or Reopened or if the there is another RefSeQ after it was CLosed or Reclosed then it should show 'No'
I would appreciate any help.
Thanks in advance
Matt
