Hi, I would like to create a new column "New Amount". The values in the new column will all come from the column "Amount", which are all positive. They new values will either remain positive or will change into negative, based on comparison of two other columns ID and ZID. The idea is that after conversion of the values in the Amount column, the total New Amount for ID (100,1) should be zero and the total New Amount for ID (200,1) should be zero as well.

Here is an example:

ID | ZID | Amount | New Amount |
---|---|---|---|

(100,1) | (100,1,1) | 10 | 10 should remain positive |

(100,1) | (100,1,2) | 10 | should convert this amount to negative 10 because value 2 from ZID (100,1,2) is the MAXIMUM value for ID (100,1) |

(200,1) | (200,1,1) | 2 | 2 should remain positive |

(200,1) | (200,1,2) | 3 | 3 should remain positive |

(200,1) | (200,1,3) | 5 | should convert this amount to negative 5 because value 3 from ZID (200,1,3) is the MAXIMUM value for ID (200,1) |