Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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
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?
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 |
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!
Excellent, just what I needed, and so simple in the end.
Thanks
Great! Glad to hear it... 🙂
Feel free to confirm my answer, so that I can get some points. 🙂