Acuumlated Per Month and Year

[#1]

Hi for all

I load a table with this fileds

LLave_Movimiento_Acum --> Is a Key formed per AccountID_Year_Month

Accountnum --> AccountID

Año_Acum --> Year

Mes_Acum --> Month

Importe --Value

I need create a filed with the Accumlated from months per year in load script

An examplo for the final table

-----

LLave_Movimiento_Acum	Accountnum	Año_Acum	Mes_Acum	Importe	Acumm
1020-201101	1020	2011	1	100	100
1020-201102	1020	2011	2	100	200
1020-201103	1020	2011	3	100	300
1020-201104	1020	2011	4	100	400
1020-201105	1020	2011	5	100	500
1020-201106	1020	2011	6	100	600
1020-201107	1020	2011	7	100	700
1020-201108	1020	2011	8	100	800
1020-201109	1020	2011	9	100	900
1020-201110	1020	2011	10	100	1000
1020-201111	1020	2011	11	100	1100
1020-201112	1020	2011	12	100	1200
1050-201101	1050	2011	1	200	200
1050-201102	1050	2011	2	200	400
1050-201103	1050	2011	3	200	600
1050-201104	1050	2011	4	200	800
1050-201105	1050	2011	5	200	1000
1050-201106	1050	2011	6	200	1200
1050-201107	1050	2011	7	200	1400
1050-201108	1050	2011	8	200	1600
1050-201109	1050	2011	9	200	1800
1050-201110	1050	2011	10	200	2000
1050-201111	1050	2011	11	200	2200
1050-201112	1050	2011	12	200	2400

-------------------------------------------

[swuehl]

Hi,

you could use peek() function to retrieve a field value from previous record, this allows you to accumulate, like

INPUT:

LOAD LLave_Movimiento_Acum,

     Accountnum,

     Año_Acum,

     Mes_Acum,

     Importe,

     Acumm as Acumm_Ref

FROM

[http://community.qlik.com/thread/39560]

(html, codepage is 1252, embedded labels, table is @1);

RESULT:

LOAD *,

if(peek(Accountnum)=Accountnum and peek([Año_Acum])=[Año_Acum],peek(Acum_Importe)+Importe,Importe) as Acum_Importe

resident INPUT order by Accountnum, [Año_Acum] asc, [Mes_Acum] asc;

drop table INPUT;

As I understood your request, I start the accumulation for each account and per year. If you don't want to reset the accumulation on a year change, just remove the "and peek([Año_Acum])=[Año_Acum]" from above if statement.

Hope this helps,

Stefan

[#3]

The answer has a problem becose in my input table i don't have the Acum_Importe Field from this paort of load expression "peek(Acum_Importe)+Importe" or the correct load expression is "peek(Importe)+Importe"?

[swuehl · Accepted solution]

Yes, your input table INPUT doesn't have this field. That's the new field we create which will hold the accumulated value for Importe (You could rename it of course, in both places).

We can reference this field with peek(), even if it's not part of the input record, because peek() accesses the resident table we create in QV.

[#5]

Thanks swuehl works fine for me app!!