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;

[jonathandienst]

Sum(if(stock_status=0, stock_value)) + If(unit_id = Previous(unit_id), Alt(Peek(Cumulative_Stock), 0)) as Cumulative_Stock

[Anonymous]

thanks, but my unit ID s are random in the table  (like 1,5,3,7,3,8,11)

they are not ordered,

[senpradip007]

stock_status filed is not there in your original load statement.

[Anonymous]

yes but I already have it in my original table, nothing changes...

[jonathandienst]

Join(stock)

LOAD unit_id,

  stock_term,

  Sum(if(stock_status=0, stock_value)) + If(unit_id = Previous(unit_id), Alt(Peek(Cumulative_Stock), 0)) as Cumulative_Stock

Resident stock

Group By unit_id, stock_term

Order By unit_id, stock_term

[senpradip007]

Have a look at this app, hope it will help.

[Anonymous]

this returns NULL

[senpradip007]

Try with

Stock:

LOAD *, if(unit_id<>Peek(unit_id), stock_value, stock_value + peek(Cumulative_Stock))  as Cumulative_Stock  Inline [

unit_id,  stock_term,  stock_value

1,         201401,          10

1,         201402,          30

1,         201403,          20

2,         201404,          40

2,         201405,          20

2,         201406,          40

];

or you can go through the below attachment

[#10]

Hi,

Try this code:

Test:

LOAD * INLINE [

    unit_id, Stock_term, Stock_value

    1, 201401, 10

    1, 201402, 30

    1, 201403, 20

    2, 201404, 40

    2, 201405, 20

    2, 201406, 40

];

NoConcatenate

Test3:

LOAD *,if(unit_id=previous(unit_id),Stock_value +peek('Cumulative Sum'),Stock_value) AS [Cumulative Sum] Resident Test Order BY unit_id,Stock_term asc;

Drop Table Test;