issue with orders qty and shipped qty

[PK422]

her the pending Quantity is  showing the same values i want output as  of (Qty-Shipped Qty) as Pending Quantity based on order id and product id

I want output in this way

Pending Qty

(4-1)=3

(3-1)=2

(2-1)=1

(4-1)=3

[Mark_Little]

How are you getting the figures you posted?

As the screenshot show what I would expect. 

[PK422]

in the screenshot, the Qty was repeating  that time I can't get the desired output of the pending Qty column and 

my expected output would be 

Pending Qty

(4-1)=3

(3-1)=2

(2-1)=1

(4-1)=3

[Aditya_Chitale · Accepted solution]

Maybe something like this ? (Tried using sample data)

test:

load * inline

[

orderID,ProductID,Qty,ShippedQty,distinguisher

509,P4,4,1,1

509,P4,4,1,2

509,P3,4,1,4

509,P4,4,1,3

];

NoConcatenate

Final:

load

orderID,

    ProductID,

    Qty,

    ShippedQty,

    distinguisher,

    if(orderID=peek(orderID) and ProductID=peek(ProductID), peek(Diff)-ShippedQty, Qty-ShippedQty) as Diff

Resident test order by orderID, ProductID asc;

drop table  test;

Output:

Regards,

Aditya