Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;
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
];
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.
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;
Thanks for your help, it worked.