Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator III
Creator III

accumulation in load script

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?

1 Solution

Accepted Solutions

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, ...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

8 Replies

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

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, ...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Creator III
Creator III

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

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, ...

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Add a where clause, something like

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Creator III
Creator III

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

Creator III
Creator III

can previous function be used to do the same calculation?

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein