Accumulation Problem

[#1]

Hello. I want to accumulate an specific column in LOAD script. My original idea was to create a grouped and organized original table... like the script below.

Entradas:

LOAD DATA,

     CODIGO_PRODUTO,

     FILIAL,

     SUM(QTDE_ENTRADA) AS QTDE_ENTRADA,

     0 AS QTDE_ACUMULADA

FROM

(qvd)

GROUP BY DATA, CODIGO_PRODUTO, FILIAL

ORDER BY DATA, CODIGO_PRODUTO, FILIAL;

After that, I was thinking I could loop through the resident table's row in order to update the QTDE_ACUMULADA with the accumulation value.

I know how to loop and how to do the  comparisons and operations to cumulate data correctly. My problem is, how do I update the resident's row ?

My key fields are DATA, CODIGO_PRODUTO, FILIAL, so i was thinking I could do a normal update, inside the loop, like

UPDATE Entradas

SET QTDE_ACUMULADA = XXXXX

WHERE Key Fields

But it doesn't seem to work that way, I wasn't able to find a way to update rows from a resident table during the load.

So... is there a way to do that ?

Thanks in advance.

[maxgro]

t:

load

  *,

alt(peek(CUM_QTDE_ENTRADA),0)+QTDE_ENTRADA as CUM_QTDE_ENTRADA;

load * inline [

CODIGO_PRODUTO, FILIAL, QTDE_ENTRADA

a, zz,100

a, xx,100

b, zz, 100

b, xx, 100

b, yy, 100

];

[#3]

Massimo, thanks for the reply. Your suggestion resulted on...

CODIGO_PRODUTO	FILIAL	QTDE_ENTRADA	CUM_QTDE_ENTRADA
a	zz	100	100
a	xx	100	200
b	zz	100	300
b	xx	100	400
b	yy	100	500

Ok, but the cumulative field should reset when CODIGO_PRODUTO AND FILIAL change... the correct result in my case would be something like this...

CODIGO_PRODUTO	FILIAL	DATA	QTDE_ENTRADA	CUM_QTDE_ENTRADA
a	zz	18/03/2015	100	100
a	zz	20/03/2015	100	200
a	xx	18/03/2015	100	100
a	xx	19/03/2015	100	200
a	xx	20/03/2015	100	300
b	zz	20/03/2015	100	100
b	xx	21/03/2015	100	100
b	yy	22/03/2015	100	100

So, data will be ordered by those 3 fields CODIGO_PRODUTO,FILIAL and DATA. Every time CODIGO_PRODUTO and FILIAL changes, the cumulative field should reset.

Is there a way to achieve this kind of result ?

Thanks again for your answer.

[maxgro · Accepted solution]

some little changes (bold)

- order by

- peek with if to reset the cum

t:

load CODIGO_PRODUTO, FILIAL, DATA ,QTDE_ENTRADA inline [

CODIGO_PRODUTO, FILIAL, DATA ,QTDE_ENTRADA ,CUM_QTDE_ENTRADA

a, zz, 18/03/2015, 100, 100

a, zz, 20/03/2015, 100, 200

a, xx, 18/03/2015, 100, 100

a, xx, 19/03/2015, 100 , 200

a, xx, 20/03/2015, 100 , 300

b, zz, 20/03/2015, 100 , 100

b, xx, 21/03/2015, 100 , 100

b, yy, 22/03/2015, 100 , 100

];

t2:

load

  *,

  if(peek(CODIGO_PRODUTO)<>CODIGO_PRODUTO or Peek(FILIAL)<>FILIAL,

  QTDE_ENTRADA, peek(CUM_QTDE_ENTRADA)+QTDE_ENTRADA) as CUM_QTDE_ENTRADA

Resident t

order by CODIGO_PRODUTO, FILIAL, DATA;

drop table t;

[#5]

Thanks for your help, it worked.