Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am attaching my excel,
here ws1 is the field which i need to devide based on quantity.
i am getting like this
Part No | Posting Date | Quantity | Total Qty | ws1 | Cumulative | outfield | |
000979940 | 4/1/2016 | 4 | 234 | 158 | 4 | 4 | |
000979940 | 3/31/2016 | 100 | 234 | 158 | 104 | 100 | |
000979940 | 3/16/2016 | 16 | 234 | 158 | 120 | 16 | |
000979940 | 3/2/2016 | 100 | 234 | 158 | 220 | 38 | |
000979940 | 2/18/2016 | 4 | 234 | 158 | 224 | 38 | |
000979940 | 2/16/2016 | 17 | 234 | 158 | 241 | 38 | |
000979940 | 5/23/2015 | -4 | 234 | 158 | 237 | 38 | |
000979940 | 5/22/2015 | -20 | 234 | 158 | 217 | 38 | |
000979940 | 5/21/2015 | -2 | 234 | 158 | 215 | 38 | |
000979940 | 5/20/2015 | -7 | 234 | 158 | 208 | 38 | |
000979940 | 5/19/2015 | -4 | 234 | 158 | 204 | 38 | |
000979940 | 5/18/2015 | -6 | 234 | 158 | 198 | 38 | |
000979940 | 5/16/2015 | -16 | 234 | 158 | 182 | 38 | |
000979940 | 5/15/2015 | -4 | 234 | 158 | 178 | 38 | |
000979940 | 5/14/2015 | -8 | 234 | 158 | 170 | 38 | |
000979940 | 5/12/2015 | -12 | 234 | 158 | 158 | -12 | |
000979940 | 11/20/2014 | 2 | 234 | 158 | 160 | 38 | |
000979940 | 10/23/2014 | 70 | 234 | 158 | 230 | 38 | |
000979940 | 10/21/2014 | 13 | 234 | 158 | 243 | 38 | |
000979940 | 5/21/2014 | -8 | 234 | 158 | 235 | 38 | |
000979940 | 11/12/2013 | -1 | 234 | 158 | 234 | 0 |
i want output like below.
my alocated it has to show 158 because ws1 is 158.
outfield should get from quantity.
Part No | Posting Date | Quantity | Total Qty | ws1 | Cumulative | outfield | |
000979940 | 4/1/2016 | 4 | 234 | 158 | 4 | 4 | |
000979940 | 3/31/2016 | 100 | 234 | 158 | 104 | 100 | |
000979940 | 3/16/2016 | 16 | 234 | 158 | 120 | 16 | |
000979940 | 3/2/2016 | 100 | 234 | 158 | 220 | 38 | |
000979940 | 2/18/2016 | 4 | 234 | 158 | 224 | 0 | |
000979940 | 2/16/2016 | 17 | 234 | 158 | 241 | 0 | |
000979940 | 5/23/2015 | -4 | 234 | 158 | 237 | 0 | |
000979940 | 5/22/2015 | -20 | 234 | 158 | 217 | 0 | |
000979940 | 5/21/2015 | -2 | 234 | 158 | 215 | 0 | |
000979940 | 5/20/2015 | -7 | 234 | 158 | 208 | 0 | |
000979940 | 5/19/2015 | -4 | 234 | 158 | 204 | 0 | |
000979940 | 5/18/2015 | -6 | 234 | 158 | 198 | 0 | |
000979940 | 5/16/2015 | -16 | 234 | 158 | 182 | 0 | |
000979940 | 5/15/2015 | -4 | 234 | 158 | 178 | 0 | |
000979940 | 5/14/2015 | -8 | 234 | 158 | 170 | 0 | |
000979940 | 5/12/2015 | -12 | 234 | 158 | 158 | 0 | |
000979940 | 11/20/2014 | 2 | 234 | 158 | 160 | 0 | |
000979940 | 10/23/2014 | 70 | 234 | 158 | 230 | 0 | |
000979940 | 10/21/2014 | 13 | 234 | 158 | 243 | 0 | |
000979940 | 5/21/2014 | -8 | 234 | 158 | 235 | 0 | |
000979940 | 11/12/2013 | -1 | 234 | 158 | 234 | 0 |
Hi Any idea.
Hi Janith,
For Straight table properties->Expressions->call "Quantity" as a expression and there you can find options Left hand side of your Expression name
3 options
No Accumulation
Full Accumulation
Partial or some values Accumulation
then u need to select Full accumulation.
use full accumulation like below
Hi perumal,
thanks for replay,
this need to done in script
HI Rama,
Thanks for replay,
This need to be done in script
What would be the output if you have multiple ws1?
ws1 is same for particular id
example:
000979940-158
000979941-170
000979942-190
Hi,
Try like this using Previous() like below
Data:
LOAD
*,
RangeSum(Previous(AccumSum), Quantity) AS AccumSum
FROM DataSource;
OR
Data:
LOAD
*,
RangeSum(Peek(AccumSum), Quantity) AS AccumSum
FROM DataSource;
Regards,
Jagan.
then try this
Data:
LOAD
[Part No]
[Posting Date]
Quantity
Total Qty
ws1
FROM Table;
Accumulate:
noconcatenate
LOAD *,
if(rowno()=1 or ws1<>previous(ws1),Quantity, rangesum(peek('Cumulative'),Quantity)) as Cumulative
Resident Data
order by ws1,[Posting Date] desc;
drop table Data;