cumulative function

[#1]

I have the following problem:

Productcode     Date              Quantity

LC09701          01-01-2012     10

LC09701          05-01-2012     -3

LC09701          20-01-2012     8

Which function can made an cumulative from the field Quantity in my Load script?

So that I get the records:

Cume

10

7

15

[lironbaram]

you can use

load Productcode,

Date,

Quantity,

if(previous(Productcode)=Productcode,numsum(peek('Cume'),Quantity),Quantity) as Cume

From

order by Productcode,Date

[jagan · Accepted solution]

Hi,

Please use the sample script below

Temp:

Load * Inline [

Productcode,     Date,              Quantity,

LC09701,          01-01-2012,    10

LC09701,          05-01-2012,     -3

LC09701,          20-01-2012,     8];

Cumulative:

LOAD Productcode,

Date,

Quantity,

if(Peek('Productcode',-1) <> Productcode, Quantity, Quantity + Peek('Acum',-1)) as Acum

Resident Temp;

DROP Table Temp;

Hope this helps you.

Regards,

Jagan.

[#4]

Hi,

When I have two mutations on one day the result is like this:

Productcode     Date              Quantity

LC09701          01-01-2012     3

LC09701          05-01-2012     -1

LC09701          05-01-2012     -1

LC09701          20-01-2012     -1

The result of the function is:

Acum

3

1

2

0

What I expect is:

3

2

1

0

[#5]

The problem was a TimeStamp field!

[jagan]

Hi,

Try using the below script

Temp:

Load * Inline [

Productcode,     Date,              Quantity,

LC09701,          01-01-2012,     3

LC09701,          05-01-2012,     -1

LC09701,          05-01-2012,     -1

LC09701,          20-01-2012,     -1];

Cumulative:

LOAD Productcode,

Date,

Quantity,

if(Previous(Productcode) <> Productcode AND Previous(Date) <> Date, Quantity, Quantity + Peek('Acum',-1)) as Acum

Resident Temp;

DROP Table Temp;

Hope this helps you.

Regards,

Jagan.