Hi team,
I am currently working on the workflow. The rule is to sum the price for the same transaction number and for the same order number. Negative prices indicate a return. But I just realize there is a trick. A transaction number might have multiple order numbers. And an order number might have multiple transaction numbers. When we do the sum, we cannot sum the duplicate part (8051.8). Do you have any suggestions on how to do this?
The data is below.
| transaction number | order num | amt |
| 5691 | 130379 | 8051.8 |
| 5749 | 130379 | -162.09 |
| 5691 | 130380 | 8051.8 |
| 5750 | 130380 | 165.14 |
| 5752 | 130380 | 24.27 |
| 5856 | 130380 | -165.14 |
| 5691 | 130378 | 8051.8 |
| 5714 | 130378 | -359.12 |
| 5751 | 130378 | -24.27 |
| 7805 | 113991 | 68.05 |
| 7811 | 113991 | 204.16 |
| 0222 | 176548 | 3183.2 |
| 0222 | 176547 | 3183.2 |
| 0040 | 150636 | 5013 |
The result is below.
8051.8+-162.09+165.14+24.27+-165.14+-359.12-24.27=7530.56
| transaction number | order num | amt | total order amt |
| 5691 | 130379 | 8051.8 | 7530.56 |
| 5749 | 130379 | -162.09 | 7530.56 |
| 5691 | 130380 | 8051.8 | 7530.56 |
| 5750 | 130380 | 165.14 | 7530.56 |
| 5752 | 130380 | 24.27 | 7530.56 |
| 5856 | 130380 | -165.14 | 7530.56 |
| 5691 | 130378 | 8051.8 | 7530.56 |
| 5714 | 130378 | -359.12 | 7530.56 |
| 5751 | 130378 | -24.27 | 7530.56 |
| 7805 | 113991 | 68.05 | 272.21 |
| 7811 | 113991 | 204.16 | 272.21 |
| 0222 | 176548 | 3183.2 | 3183.23 |
| 0222 | 176547 | 3183.2 | 3183.23 |
| 0040 | 150636 | 5013 | 5012.97 |
