accumulate with load

[Anonymous]

hi community,

ı have a data as;

stock value , customer ,      term

10                    a              201401

20                    a               201401

30                    b               201402

40                    c               201403

50                    c               201404

how can we make accumulative sum by term, with load statement?

ı want ıt to be seen as:

term           stock value    

201401            30

201402            60

201403            100

201404            150

thanks

[sunny_talwar]

Try the following script:

Table:

LOAD * INLINE [

    Stock Value, Customer, Term

    10, A, 201401

    20, A, 201401

    30, B, 201402

    40, C, 201403

    50, C, 201404

];

Join(Table)

LOAD Term,

  Sum([Stock Value]) + RangeSum(Alt(Peek('Accumulated Stock Value'), 0)) as [Accumulated Stock Value]

Resident Table

Group By Term;

Output looks like this:

HTH

Best,

Sunny

[Anonymous]

but ı already get the data from SQL,

Table:

LOAD * INLINE [

    Stock Value, Customer, Term

    10, A, 201401

    20, A, 201401

    30, B, 201402

    40, C, 201403

    50, C, 201404

];

do ı need to take it again like that? ı already have the data from sql query.

[anbu1984]

T1:
Load * Inline [
stock value, customer ,      term
10,a,201401
20,a,201401
30,b,201402
40,c,201403
50,c,201404 ];

T2:
Load term,[stock value]+Alt(Peek(CumSum),0) As CumSum;
Load term,sum([stock value]) As [stock value] Resident T1 Group By term Order By term;

[sunny_talwar · Accepted solution]

No you don't. Since I don't have access to your data source, I used a inline load to get that into the application. You can use this part:

Join(Table) //Replace Table with your fact table you are bringing from SQL

LOAD Term,

  Sum([Stock Value]) + RangeSum(Alt(Peek('Accumulated Stock Value'), 0)) as [Accumulated Stock Value]

Resident Table //Replace Table with your fact table you are bringing from SQL

Group By Term;

HTH

Best,

Sunny

[Anonymous]

ıt says table not found:

new_table:

Join(stok)

LOAD STOK_DONEM,

  Sum(Stok_Bedeli) + RangeSum(Alt(Peek('Accumulated Stock Value'), 0)) as [Accumulated Stock Value]

Resident stok

Group By STOK_DONEM

[sunny_talwar]

Table not found? Which table? stok??

Can you share your script for us to look at it?

Best,

Sunny

[Anonymous]

yes my main table taking data from oracle is STOK

stok:

select

Stok_Bedeli, // stock value

STOK_DONEM, // term

from ......... ;

ADD

new:

Join(stok) //Replace Table with your fact table you are bringing from SQL

LOAD STOK_DONEM,

  Sum(Stok_Bedeli) + RangeSum(Alt(Peek('Accumulated Stock Value'), 0)) as [Accumulated Stock Value]

Resident stok //Replace Table with your fact table you are bringing from SQL

Group By STOK_DONEM;

[anbu1984]

Use Order by clause if term field is not ordered in your sql

[sunny_talwar]

Do you happen to have another table in your script which have the exact same fields as stok? The reason I ask you this is because it seems that QV is not creating the table stok and the only reason I think it might be creating it could be because there is another table with the same field (and field names) and its concatenating this stok table to that table.

And I think anbu cheliyan brings a good point, although unrelated to current problem, that you are probably better of using a order by statement also, just to make sure the accumulation happens correctly.

Sunny