Loading accumulate totals

[#1]

Hi, I am trying to load a full accumulate in the data load script.

Example table:

Key, ItemPrice

1, 10

2, 20

3, 10

4, 100

5, 5

Desired output is:

Key, ItemPrice, CumTotal

1, 10, 10

2, 20, 30

3, 10, 40

4, 100, 140

5, 5, 145

How do I achieve this in the load script? I can't seem to get the peek and previous functions to work in my favour, always giving some weird results.

I also have a where clause in my dataset, I understand this may affect the functions used.

James.

[nagaiank · Accepted solution]

Try the following script, which works.

LOAD  *,ItemPrice+If(IsNull(Peek(CumItemPrice)),0,Peek(CumItemPrice)) as CumItemPrice;
LOAD * Inline [
Key, ItemPrice
1, 10
2, 20
3, 10
4, 100
5, 5

];

[its_anandrjs]

Then try with this

Raw:

LOAD * INLINE [

    Key, ItemPrice

    1, 10

    2, 20

    3, 10

    4, 100

    5, 5

];

Final:

LOAD

Key,

ItemPrice, if(RowNo() = 1,ItemPrice,ItemPrice + Peek(CumTotal)) as CumTotal

Resident Raw Order By Key;

DROP Table Raw;

[#4]

Final:

LOAD

Key,

ItemPrice, if(RowNo() = 1,ItemPrice,ItemPrice + Peek(AccItem)) as CumTotal

Resident Raw Order By Key;

DROP Table Raw;

In the above, why do you have Peek(AccItem).

AccItem isn't a field in this datamodel?

[its_anandrjs]

Oh Sorry no it is CumTotal field check now i update

Final:

LOAD

Key,

ItemPrice, if(RowNo() = 1,ItemPrice,ItemPrice + Peek(CumTotal)) as CumTotal

Resident Raw Order By Key;

DROP Table Raw;

[jonathandienst]

Hi

You can simplify this by dropping the preceding load and using Alt():

LOAD *,

       ItemPrice+Alt(Peek(CumItemPrice),0) As CumItemPrice

Inline

[

  Key, ItemPrice

  1, 10

  2, 20

  3, 10

  4, 100

  5, 5

];

Peek() reads the value from the previous output row. Previous reads the value from the previous input row.

HTH

Jonathan

[#7]

This is correct and working as per my simplified example, can you explain why(how?) you're Peek()-ing a field which doens't exist yet? Surely you should Peek(ItemPrice)

It works but I have no idea why Hoping for elaboration so I can implement this into my bigger solution, but even if not many thanks for your time and help.

[#8]

My actual dataset is structured differently, how would this be different if I had

LOAD %Key,

          dim_Something,

          measure_Something,

          measure_ItemPrice

FROM spreadsheet

WHERE testSomething>=1;

I have tried

LOAD %Key,

          dim_Something,

          measure_Something,

          measure_ItemPrice,

          measure_ItemPrice+Alt(Peek(CumItemPrice),0) As CumItemPrice

FROM spreadsheet

WHERE testSomething>=1;

but feel I'm really missing the point of what you're doing.