accumulation in load script

[ronaldwang]

In the attached sample file I want to create an additional column which is accumulated value of Dividend paid, basically I want to create a fifth column where it will scan through fourth column and add up those dividends paid up to date. how can I achieve it in the load script?

[jonathandienst]

This the pattern:

LOAD ...

  Dividend,

  RangeSum(Peek(Accumulated_Dividend), Dividend) as Accumulated_Dividend,

  ...

Resident Data

Order by ...

Load the data from the source, then perform an ordered resident load to get the accumulation

[jonathandienst · Accepted solution]

If you want to reset the accumulation for each value of another field (example [Branch]), then use

LOAD ...

  Branch,

  Dividend,

  If(Branch = Previous(Branch),

  RangeSum(Peek(Accumulated_Dividend), Dividend),

  Dividend) as Accumulated_Dividend,

  ...

Resident Data

Order by Branch, ...

[ronaldwang]

Hi Jonathan, how if i want to put one more condition, say date greater than 01/01/2015.

[Anil_Babu_Samineni]

May be use Where Condition like

LOAD ...

  Branch,

  Dividend,

  DateField,

  If(Branch = Previous(Branch),

  RangeSum(Peek(Accumulated_Dividend), Dividend),

  Dividend) as Accumulated_Dividend,

  ...

Resident Data Where DateField >= '01/01/2015'

Order by Branch, ...

[jonathandienst]

Add a where clause, something like

Where [Date] > Date#('01/01/2015', 'dd/MM/yyyy')

[ronaldwang]

by not putting the where condition, it is working perfectly, after putting the where condition, first few rows getting strange result.

[ronaldwang]

can previous function be used to do the same calculation?

[jonathandienst]

They are not interchangeable:

Previous() gets the value from the prior line in the source or input data.

Peek() looks at the prior line in the output data.