Hello - I have a situation where I want to apply an "Accrual Indicator" to rows that have the same payment reference numbers. The "Accrual Indicator" related to these payments can have a value of "Y", "N" or null. There are situations where there may be duplicate payment reference numbers but only one of the payment reference numbers has a "Y" or an "N". In that situation I want to apply the "Y" and "N" to all similar payment reference numbers, but if its they are all null then they should remain null. For example, data may start like this:
Payment Reference Number | Accrual Indicator (Y/N) |
1000 | Y |
1002 | |
1005 | N |
1000 | |
1000 | |
1006 | Y |
1007 | |
1008 | N |
1008 | |
1008 | |
1006 |
And I want it to end like this:
Payment Reference Number | Accrual Indicator (Y/N) |
1000 | Y |
1002 | |
1005 | N |
1000 | Y |
1000 | Y |
1006 | Y |
1007 | |
1008 | N |
1008 | N |
1008 | N |
1006 | N |
I'm guessing this can be achieved through the multi row tool, but I'm not sure how to search for rows that have similar reference numbers.
I made an error in the above data. The last payment reference number (1006) should be marked with a "Y" and not an "N".
@matula23 one way of doing this
This solution is almost where I need it to be, but I'm running into a problem where I may have have multiple payment reference numbers with an accrual indicator, and when I get to the first filter I have duplicate payment reference numbers and then I end up with more rows that I started in the Join tool. For example, see the updated data set:
Payment Reference Number | Accrual Indicator (Y/N) |
1000 | Y |
1002 | |
1005 | N |
1000 | Y |
1000 | |
1006 | Y |
1007 | |
1008 | N |
1008 | |
1008 | |
1006 |
In this example, the payment reference number "1000" occurs 3 times. 2 of the times it has an indicator of "Y" and one time it is null. I want to fill that last null with a "Y", but if i use the above method then I believe I will duplicate my rows because the right side of the join has 2 "1000" references with an indicator of "Y".
@matula23 use a unique tool to remove the duplicate after the filter tool