Accumulation in Script, Help Required

[#1]

I have a simple script (part of) to extract our future sales schedule. What I would like to do is accumulate the sales order quantity for each product through the script. I guess I need to use the INLINE function but I not sure what I need to do. The reason for doing this is so I can determine when the stock for each part will be depleted showing which sales orders can be fulfilled.

My script is as follows

load

customer,

schedule_number,

order_line_no,

product,

description,

value,

warehouse,

order_qty as sales_order_qty,

required_date as sales_required_date,

currency,

exchange_rate,

order_book_value,

fin_period,

fin_year,

fin_week,

alpha,

name,

firm_planned;

ODBC CONNECT TO [xxxxxxx];

SQL SELECT *

FROM xxxx.dbo.op_orderbook_complete;

thanks in advance.

[#2]

No, you need the SUM function in a RESIDENT load. Something like this:

Data:
SQL SELECT
Product
,Sales_QTY
FROM table;
SumSales:
LOAD
Product
,SUM(Sales_QTY) AS SumSales_QTY
RESIDENT Data
GROUP BY Product;

[#3]

Thanks, not sure how I need to put this into my existing script, also can you confirm if I have 5 sales order lines for the same product each for 10 off that line 3 will show 30 and line 4 will show 40 etc.

thanks

[#4]

Can you show a table with some sample data of what is it that you want to do, because it seems like you want to accumulate the values, but keep all transactions?

[#5]

As you say I need to retain all the data as below but require an additional field with the cumulative quantity for that product in date order. I can then compare each cumulative quantity against the stock quantity to determine if we have sufficient stock levels and to what point in time.

6G33-611B10-BE	3182.06	49	21.29	28/03/2011 00:00:00
6G33-611B10-BE	1818.32	28	21.29	04/04/2011 00:00:00
6G33-611B10-BE	1818.32	28	21.29	11/04/2011 00:00:00
6G33-611B10-BE	909.16	14	21.29	18/04/2011 00:00:00
6G33-611B10-BE	909.16	14	21.29	02/05/2011 00:00:00
6G33-611B10-BE	2727.48	42	21.29	09/05/2011 00:00:00
6G33-611B10-BE	3636.64	56	21.29	16/05/2011 00:00:00
6G33-611B10-BE	6818.7	105	21.29	13/06/2011 00:00:00
6G33-611B10-BE	3636.64	56	21.29	11/07/2011 00:00:00
6G33-611B10-BE	8637.02	133	21.29	15/08/2011 00:00:00
6G33-611B10-BE			21.29

[#6]

Then things will look a little different and you will need a succeeding load:

Data:
LOAD
 *
 ,IF(PREVIOUS(Product)=Product,PEEK('QTY_ACC'),0)+QTY AS QTY_ACC;
SQL SELECT
 Product
 ,Date
 ,QTY
FROM Table
ORDER BY Product,Date;

The ORDER BY is important!

[#7]

Excellent, just what I needed, and so simple in the end.

Thanks

[#8]

Great! Glad to hear it... 🙂

Feel free to confirm my answer, so that I can get some points. 🙂