Split the values into two column.

Community Support
1

Hi, 

I have let say table as below 

    ID               BasketID              Quantity
------------   ------------------    --------------------
QQQ             QQQB                   45
QQQ             QQQS                   100

DDD              DDDS                    50
ZZZ                ZZZS                     12
ZZZ                ZZZB                    80

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’

Output  :

ID               SellBaksetQTY     BuyBasketQTY
-----------     ----------------------   ---------------------
QQQ                100                       45
DDD                 50                         0
ZZZ                   12                        80

Any suggestion is appreciated. 

2

You can use the pivot table guide on code.kx.com like so:

q)show t:([]ID:QQQQQQDDDZZZZZZ;BasketID:QQQBQQQSDDDSZZZSZZZB;Quantity:45 100 50 12 80);ID BasketID Quantity---------------------QQQ QQQB 45QQQ QQQS 100DDD DDDS 50ZZZ ZZZS 12ZZZ ZZZB 80q)show t:update p:?[BasketID like "*S";SellBasketQty;BuyBasketQty] from t;ID BasketID Quantity p-----------------------------------QQQ QQQB 45 BuyBasketQtyQQQ QQQS 100 SellBasketQtyDDD DDDS 50 SellBasketQtyZZZ ZZZS 12 SellBasketQtyZZZ ZZZB 80 BuyBasketQtyq)show P:exec distinct p from t;BuyBasketQtySellBasketQtyq)0^exec P#(p!Quantity) by ID:ID from tID | BuyBasketQty SellBasketQty---| --------------------------DDD| 0 50QQQ| 45 100ZZZ| 80 12

3

What you need is basically a pivot https://code.kx.com/q/kb/pivoting-tables 

q){exec distinct[x`BasketID]#(BasketID!Quantity) by ID:ID from x} update BasketID:?[BasketID like “*B”;`buy;`sell] from tID buy sell
DDD     50  
QQQ 45  100
ZZZ 80  12 
4

You can accomplish this without a pivot:

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