topic Re: accumulation in load script in QlikView

accumulation in load script

ronaldwang — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: accumulation in load script

jonathandienst — Fri, 12 Jan 2018 04:50:09 GMT

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

Re: accumulation in load script

jonathandienst — Fri, 12 Jan 2018 04:52:38 GMT

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

Re: accumulation in load script

ronaldwang — Fri, 12 Jan 2018 04:57:26 GMT

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

Re: accumulation in load script

Anil_Babu_Samineni — Fri, 12 Jan 2018 05:02:12 GMT

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

Re: accumulation in load script

jonathandienst — Fri, 12 Jan 2018 05:02:22 GMT

Add a where clause, something like

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

Re: accumulation in load script

ronaldwang — Fri, 12 Jan 2018 05:16:16 GMT

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

Re: accumulation in load script

ronaldwang — Fri, 12 Jan 2018 05:44:28 GMT

can previous function be used to do the same calculation?

Re: accumulation in load script

jonathandienst — Mon, 15 Jan 2018 05:30:45 GMT

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.