In BasketID column the last index tell that whether it is buy or sell basket (LIKE : QQQB is buy Basket). Now I want output such that I have columns as ID, BuyBasket Quantity, Sell Basket Quantity. and if any of the quantity is not present it should be set as 0 or ‘NA’
q)0!select BuyBasketQty:sum ?["B"=last each string BasketID;Quantity;0], SellBasketQty:sum ?["S"=last each string BasketID;Quantity;0] by ID from tID BuyBasketQty SellBasketQty------------------------------DDD 0 50QQQ 45 100ZZZ 80 12
If you want to make it more performant:
q)t:update c:last each string BasketID from tq)0!select BuyBasketQty:sum ?["B"=c;Quantity;0], SellBasketQty:sum ?["S"=c;Quantity;0] by ID from tID BuyBasketQty SellBasketQty------------------------------DDD 0 50QQQ 45 100ZZZ 80 12