Running Totals for Multiple Part Numbers

[johnca]

I have a script that pulls from a QVD and am counting a running total. Here's an excerpt;

Ship:

Load

     Part_Number,

     Serial_Number,

     Ship_Date,

     Ship_Quantity,

     If(IsNull(Previous(Ship_Quantity)),Ship_Quantity,Peek('Ship_Running_Total') + Ship_Quantity) as Ship_Running_Total

From Ship.qvd;

What it does it does well, but it lumps all the part numbers together so that if I select a specific part number I get the running total of all part numbers, not just the one I am interested in.

How can I get the running total for each part number?

Is a For-Next loop the way to do it? If so, how would that work?

Many regards,

John

[Gysbert_Wassenaar · Accepted solution]

You can use a resident load to order the table by Part_Number and Ship_Date so you can calculate the running totals per Part_Number:

Ship:

Load

     Part_Number,

     Serial_Number,

     Ship_Date,

     Ship_Quantity,

From Ship.qvd;

Result:

load

    Part_Number

   ,Serial_Number

    ,Ship_Date

    ,Ship_Quantity

    ,if(previous(Part_Number)=Part_Number,

        rangesum(peek('Ship_Running_Total'),Ship_Quantity),

        Ship_Quantity

       ) as Ship_Running_Total

resident Ship

order by Part_Number,Ship_Date;

drop table Ship;

[johnca]

☺ I knew that.

Thanks Gysbert!