Hi k2shean, You can use something like the script below. I have loaded your table inline just for test purposes. Of course you would really load it from its proper source. This solution gives one row in the Agg table. If you have higher dimensions, use Group By as jagan mohan suggests. In this case you need to Load all the fields you group by.
load * inline [
sum(if(match( CATEGORY , '123' , '456' ,'789')>0, AMT,0)) - sum(if(match( CATEGORY , 'abc' , 'def')>0, AMT,0)) as AggrAmt