Grouping and Filtering Question

iudogie

Let's say I have a system in which a single payment ID may be associated with multiple documents. If the payment ID is only associated with one document, I just want it to flow through with the associated status. If the payment ID is associated with multiple documents, I want to use the following logic:

If a payment ID has multiple documents:

• Check the statuses of all the documents
  • If any of the statuses are not REJECTED or DUPLICATE, use that status
  • If the statuses are all REJECTED, use REJECTED
  • If the statuses are all DUPLICATE, use DUPLICATE
  • If the statuses are a mixture of REJECTED or DUPLICATE, use REJECTED status

Here is the sample data:

Payment ID	Document ID	Status	Employee Name
123	7410	Paid	Lael Odonnell
123	14523	Rejected	Lael Odonnell
789	45593	In Transit	Tanek Cardenas
676	10390	Expired	Deborah Lester
456	36213	Duplicate	Melyssa Frost
456	62231	Rejected	Melyssa Frost

Here is how the data would look after the transformation:

Payment ID	Document ID	Status	Employee Name
123	7410	Paid	Lael Odonnell
789	45593	In Transit	Tanek Cardenas
676	10390	Expired	Deborah Lester
456	62231	Rejected	Melyssa Frost

Thanks for any help.

Jean-Balteryx

Hi @iudogie ,

Here is an alternative solution !

status_assignments.yxmd