Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
☺ I knew that.
Thanks Gysbert!