Designing a job to read Item/Lot Inventory data, transform it, and insert IC Adjustments to write it off. Have done several IC Adjustment jobs so should be straight forward with one wrinkle: Sometimes these Item/Lot records have positive quantity and negative cost (or vice versa) and therefore I'll need to split this record into two separate rows as you can't adjust quantity and cost in opposite directions on a single detail line.

It seems like the Aggregate could do this splitting (and deleting of the original record)?

Data would look like this to start...

Item: 00500
Lot: PACKED ON NOV 2 2020
Loc: CHENON
Qty: 100
Cost: -50

And I'd want to end up Adjusting as..

(Line 1)
Item: 00500
Lot: PACKED ON NOV 2 2020
Loc: CHENON
Qty: 100
Cost: 0
Adj Type: Decrease Quantity

(Line 2)

Item: 00500
Lot: PACKED ON NOV 2 2020
Loc: CHENON
Qty: 0
Cost: 50
Adj Type: Increase Cost

I'm not savvy with Aggregates, and I'm playing with it and looking at the tutorial, but I'm not quite sure I get the concepts for what I'd need to do with the formulas to create the two calc records from the single source one.

Anyone have experience splitting lines like this?