Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
johnca
Specialist
Specialist

Running Totals for Multiple Part Numbers

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand
johnca
Specialist
Specialist
Author

☺ I knew that.

Thanks Gysbert!