RangeSum Issue

[scotly-victor]

Hi Every One,

I  wanted  a Rangesum of Qty by Itemcode ,Branch.

Input:

LOAD * Inline [

Date,Itemcode,Branch,Qty

1/1/2013,A,112,7

1/1/2013,A,112,14

3/1/2013,A,113,10

4/1/2013,A,112,23

5/1/2013,B,112,23

5/1/2013,B,112,24

];

Desired Output:

LOAD * Inline [

Date,Itemcode,Branch,runningQty

1/1/2013,A,112,7

1/1/2013,A,112,21

3/1/2013,A,113,10

4/1/2013,A,112,44

5/1/2013,B,112,23

5/1/2013,B,112,47

];

Code:

Final:

load

Date,

Itemcode,

Branch,

if(Itemcode=peek(Itemcode) and Branch=Peek(Branch),rangesum(Qty,peek('runningQty') ),Qty) as runningQty

resident

Temp  order by Itemcode,Branch;

Above code didn't work.

[swuehl · Accepted solution]

I think you need to sort your Final table LOAD by date ascending, too:

Final:

load distinct

Date,

Itemcode,

Branch,

if(Itemcode=peek(Itemcode) and Branch=Peek(Branch),rangesum(Qty,peek('runningQty') ),Qty) as runningQty

resident

Temp  order by Itemcode,Branch, Date;

edit:

Input:

LOAD * Inline [

Date,Itemcode,Branch,Qty

1/1/2013,A,112,7

1/1/2013,A,112,14

3/1/2013,A,113,10

4/1/2013,A,112,23

5/1/2013,B,112,23

5/1/2013,B,112,24

];

Final:

load distinct

Date,

Itemcode,

Branch,

if(Itemcode=peek(Itemcode) and Branch=Peek(Branch),rangesum(Qty,peek('runningQty') ),Qty) as runningQty

resident

Input  order by Itemcode,Branch,Date;

DROP TABLE Input;

Branch	Itemcode	Date	runningQty
112	A	1/1/2013	7
112	A	1/1/2013	21
112	A	4/1/2013	44
112	B	5/1/2013	23
112	B	5/1/2013	47
113	A	3/1/2013	10