topic Accumulation in Script, Help Required in QlikView

Accumulation in Script, Help Required

Fri, 25 Mar 2011 11:32:50 GMT

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.

Accumulation in Script, Help Required

Fri, 25 Mar 2011 11:43:43 GMT

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;

Accumulation in Script, Help Required

Fri, 25 Mar 2011 11:51:26 GMT

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

Accumulation in Script, Help Required

Fri, 25 Mar 2011 11:54:20 GMT

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?

Accumulation in Script, Help Required

Fri, 25 Mar 2011 12:02:14 GMT

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

Accumulation in Script, Help Required

Fri, 25 Mar 2011 12:15:49 GMT

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!

Accumulation in Script, Help Required

Fri, 25 Mar 2011 12:41:06 GMT

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

Thanks

Accumulation in Script, Help Required

Fri, 25 Mar 2011 12:50:46 GMT

Great! Glad to hear it... 🙂

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