Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 |
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 |