Apply cell value to similar rows

Question

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 NumberAccrual Indicator (Y/N)1000Y1002 1005N1000 1000 1006Y1007 1008N1008 1008 1006

And I want it to end like this:

Payment Reference NumberAccrual Indicator (Y/N)1000Y1002 1005N1000Y1000Y1006Y1007 1008N1008N1008N1006N

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.

binu_acs · Answer

@matula23 use a unique tool to remove the duplicate after the filter tool

Accural_Indicator_23042024.yxmd

matula23 · Answer

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 NumberAccrual Indicator (Y/N)1000Y1002 1005N1000Y1000 1006Y1007 1008N1008 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".

binu_acs · Answer

@matula23 one way of doing this

Accural_Indicator_23042024.yxmd