Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Accumulation in Script, Help Required

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.

7 Replies
Not applicable
Author

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;


Not applicable
Author

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

Not applicable
Author

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?

Not applicable
Author

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-BE3182.064921.2928/03/2011 00:00:00
6G33-611B10-BE1818.322821.2904/04/2011 00:00:00
6G33-611B10-BE1818.322821.2911/04/2011 00:00:00
6G33-611B10-BE909.161421.2918/04/2011 00:00:00
6G33-611B10-BE909.161421.2902/05/2011 00:00:00
6G33-611B10-BE2727.484221.2909/05/2011 00:00:00
6G33-611B10-BE3636.645621.2916/05/2011 00:00:00
6G33-611B10-BE6818.710521.2913/06/2011 00:00:00
6G33-611B10-BE3636.645621.2911/07/2011 00:00:00
6G33-611B10-BE8637.0213321.2915/08/2011 00:00:00
6G33-611B10-BE21.29


Not applicable
Author

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!

Not applicable
Author

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

Thanks

Not applicable
Author

Great! Glad to hear it... 🙂

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