running total by load

[Anonymous]

Hi friends,

I have a load query,

(I already have a table 'stock' loaded from oracle and  I want to make it cumulative sum with a load query,

my table is like that :

unit_id  stock_term  stock_value

1         201401          10

1         201402          30

1         201403          20

2         201404          40

2         201405          20

2         201406          40

ı want to convert it to :

(ordered by stock_term)

unit_id  stock_term  cumulative_stock_value

1         201401          10

1         201402          40

1         201403          60

2         201404          40

2         201405          60

2         201406          100

this below statement is false,

what is the mistake that I make?

Join(stock)

LOAD unit_id, stock_term,

  Sum(if(stock_status=0,stock_value)) + RangeSum(Alt(Peek('Cumulative_Stock'), 0))

  as Cumulative_Stock

Resident stock

Group By unit_id, stock_term

Order By stock_term;

[Anonymous]

thanks but none of them solved the problem, all have errors, return null etc...

[jonathandienst]

fisland92 wrote:

this returns NULL

Not sure what that means. You will need to be a bit more specific. Test the LOAD statement without the join. Check that the values of stock_status are what you are expecting - at least one record should have a stock_status of 0 for every unit_id.