Hello Alteryx community,
I am trying to solve a problem and it would be really helpful if you could provide me any suggestions.
I am trying to derive New ID and New Quantity columns which are based on O, R, C columns. The logic is, as long as O quantity is less than R Quantity the New Quantity column should popluate number from O Quantity column (rows a to d) and simultaneously reduce the quantity in R Quantity column (see Reduced R Quantity column). Whenever R Quantity is completely exhausted and if the O quantity row has additional quantity left (row 'e'), this additional quantity should be split into new line (row 'e' second time) and if there are additional rows (row 'f') then these should start using C Quantity. New ID column should be populated based on R id or C id, from whichever column its picking up the quantity. R Quantity and C Quantity are basically total quantity associated to sec id ABC that is being listed here multiple times.
I am attaching the excel as well below for this data.
| Row | Sec ID | O Quantity | R ID | R Quantity | C ID | C Quantity | New ID | New Quantity | Reduced R Quantity |
| a | ABC | 9.013 | 1 | 39.303 | 2 | 9.013 | 1 | 9.013 | 30.29 |
| b | ABC | 0.742 | 1 | 39.303 | 2 | 9.013 | 1 | 0.742 | 29.548 |
| c | ABC | 0.094 | 1 | 39.303 | 2 | 9.013 | 1 | 0.094 | 29.454 |
| d | ABC | 0.712 | 1 | 39.303 | 2 | 9.013 | 1 | 0.712 | 28.742 |
| e | ABC | 33.984 | 1 | 39.303 | 2 | 9.013 | 1 | 28.742 | 5.242 |
| e | ABC | 33.984 | | 39.303 | | 9.013 | 2 | 5.242 | 3.771 |
| f | ABC | 3.771 | 1 | 39.303 | 2 | 9.013 | 2 | 3.771 | |
