Hi Alteryx Gurus,
I am stuck with applying the a logic in Alteryx, please share some thoughts. Any advise is much appreciated!
I have customers with open deductions and overpayments, so the goal is offset the deduction items by the overpayments items, below are two customer examples(with expected result), and I need a logic to create the column J - "Original amt uploaded to Web ADI Flag" and "Uploaded amount" in Alteryx.
LOGIC:
If column E - "DED offset completed flag" is "N" (example- customer ABC Inc), column J will apply all "Y" to the overpayment items( as all overpayments are going to be used for offset), and uploaded amount should be the same as Column G - remaining amount, but for deduction items, it will look up the items in order by claim age (oldest first) for offset. In this case, total OPM is -$404 (column 
, so we are only need to offset $404 of deduction, therefore, for uploaded amount, I need to put $404 there ( instead of original amount $638), and for the other two deductions left there, the uploaded amount should be 0.
Another situation, for customer 123 solution below, is in the opposite case, where there are more overpayments than deductions, so then for column J, all deduction items will apply "Y", but for overpayment, it will look the offset by claim aging (column). The oldest one of - $151.41 could be used fully, and second oldest one of -$480,20 will only be used with amount of $141.86 partially. The total of one customer in "uploaded amount" needs to be "0". (My biggest challenge is here to create a logic if there are multiple items could be used to offset but not the full amount and follows the rule by claim age (old first)….
I am assuming that multiple rows formula might help in this case, but not sure how to do it... Please help and advise.
thank you thank you!
Flora
