Counting sales over period in load script.

[#1]

Hi to everyone.
I have two tables:

Sales:LOAD date(Date) as Date, Product, Sales INLINE [ Date, Product, Sales 01.04.2010, Prod1, "297,77" 01.04.2010, Prod2, "889,36" 01.04.2010, Prod3, "313,52" 02.04.2010, Prod1, "126,18" 02.04.2010, Prod2, "656,22" 02.04.2010, Prod3, "624,99" 03.04.2010, Prod1, "823,88" 03.04.2010, Prod2, "392,52" 03.04.2010, Prod3, "561,32" 04.04.2010, Prod1, "265,35" 04.04.2010, Prod2, "924,12" 04.04.2010, Prod3, "990,04" 05.04.2010, Prod1, "254,63" 05.04.2010, Prod2, "106,09" 05.04.2010, Prod3, "843,05" 06.04.2010, Prod1, "657,90" 06.04.2010, Prod2, "309,20" 06.04.2010, Prod3, "524,59" 07.04.2010, Prod1, "549,02" 07.04.2010, Prod2, "866,21" 07.04.2010, Prod3, "738,24"];Products:LOAD * INLINE [ Product, Period Prod1, 3 Prod2, 4 Prod3, 5];

I need to get in load script a table that will have a counted sum of sales for this product over the period that is defined for ecery product.
For example - I'll have in a row:
Date=01.04.2010; Product=Prod1; SalesOverPeriod=297,77 that is: 0+0+297,77
Date=06.04.2010; Product=Prod2; SalesOverPeriod=1731,93 that is: 392,52+924,12+106,09+309,20

Full table must look like:

SalesOverPeriiod:LOAD * INLINE [ Date, Product, SalesOverPeriod 01.04.2010, Prod1, "297,77" 01.04.2010, Prod2, "889,36" 01.04.2010, Prod3, "313,52" 02.04.2010, Prod1, "423,95" 02.04.2010, Prod2, "1545,58" 02.04.2010, Prod3, "938,51" 03.04.2010, Prod1, "1247,83" 03.04.2010, Prod2, "1938,10" 03.04.2010, Prod3, "1499,83" 04.04.2010, Prod1, "1215,41" 04.04.2010, Prod2, "2862,22" 04.04.2010, Prod3, "2489,87" 05.04.2010, Prod1, "1343,86" 05.04.2010, Prod2, "2078,95" 05.04.2010, Prod3, "3332,92" 06.04.2010, Prod1, "1177,88" 06.04.2010, Prod2, "1731,93" 06.04.2010, Prod3, "3543,99" 07.04.2010, Prod1, "1461,55" 07.04.2010, Prod2, "2205,62" 07.04.2010, Prod3, "3657,24"];

[martin59]

Hello,

Why don't you accumulate your values in a graphic object ?

You can enter this expression :

sum(sales)

check this option :

With Products and Date as dimensions

Martin

[#3]

Because I need to work with this table doing some more calculations, and because in my real table there are more than 300 dates, something like 2k products and every product has a different period for counting - starting from 7 to 120. And one more - for every date and product I need to have their uniqe SalesOverPeriod sum.

[martin59]

So you can do this :

tmpSales:
LOAD date#(Date,'DD.MM.YYYY') as Date,
 Product,
 Sales
INLINE [
 Date, Product, Sales
 01.04.2010, Prod1, "297,77"
 01.04.2010, Prod2, "889,36"
 01.04.2010, Prod3, "313,52"
 02.04.2010, Prod1, "126,18"
 02.04.2010, Prod2, "656,22"
 02.04.2010, Prod3, "624,99"
 03.04.2010, Prod1, "823,88"
 03.04.2010, Prod2, "392,52"
 03.04.2010, Prod3, "561,32"
 04.04.2010, Prod1, "265,35"
 04.04.2010, Prod2, "924,12"
 04.04.2010, Prod3, "990,04"
 05.04.2010, Prod1, "254,63"
 05.04.2010, Prod2, "106,09"
 05.04.2010, Prod3, "843,05"
 06.04.2010, Prod1, "657,90"
 06.04.2010, Prod2, "309,20"
 06.04.2010, Prod3, "524,59"
 07.04.2010, Prod1, "549,02"
 07.04.2010, Prod2, "866,21"
 07.04.2010, Prod3, "738,24"
];

Products:
LOAD * INLINE [
 Product, Period
 Prod1, 3
 Prod2, 4
 Prod3, 5
];

Sales:
LOAD Product,
 Date,
 Sales,
 if(Product=peek('Product'),peek('SalesOverPeriod')+Sales,Sales) as SalesOverPeriod;
LOAD *,
 0 as SalesOverPeriod
RESIDENT tmpSales
ORDER BY Product, Date;

DROP TABLE tmpSales;

[#5]

Umh thanks but... In this table you make a full accumulation, and I don't need full accumulation, I need an accumulation over the defined period - as in table "Products" - for Prod1 - 3 last days, for Prod2 - 4 last days, for Prod3 - 5 last days etc.

[#6 · Accepted solution]

Using your idea got the right script:

tmpSales:LOAD date#(Date,'DD.MM.YYYY') as Date, Product, SalesINLINE [ Date, Product, Sales 01.04.2010, Prod1, "297,77" 01.04.2010, Prod2, "889,36" 01.04.2010, Prod3, "313,52" 02.04.2010, Prod1, "126,18" 02.04.2010, Prod2, "656,22" 02.04.2010, Prod3, "624,99" 03.04.2010, Prod1, "823,88" 03.04.2010, Prod2, "392,52" 03.04.2010, Prod3, "561,32" 04.04.2010, Prod1, "265,35" 04.04.2010, Prod2, "924,12" 04.04.2010, Prod3, "990,04" 05.04.2010, Prod1, "254,63" 05.04.2010, Prod2, "106,09" 05.04.2010, Prod3, "843,05" 06.04.2010, Prod1, "657,90" 06.04.2010, Prod2, "309,20" 06.04.2010, Prod3, "524,59" 07.04.2010, Prod1, "549,02" 07.04.2010, Prod2, "866,21" 07.04.2010, Prod3, "738,24"];Products:LOAD * INLINE [ Product, Period Prod1, 3 Prod2, 4 Prod3, 5];Left join (tmpSales) load * resident Products;Sales:LOAD Product, Date, Sales, if(Product=peek('Product'),peek('SalesOverPeriod')+Sales-if(Product=peek('Product',-Period),peek('Sales',-Period),0),Sales) as SalesOverPeriod;LOAD *, 0 as SalesOverPeriodRESIDENT tmpSalesORDER BY Product, Date;DROP TABLE tmpSales;

thnx, Martin.