Sum function in a for loop/Group by

[bryan_21]

Hello, I am using for loop and group by function to sum the value of a field if they are the same. The for loop and group by function is working but I've noticed that when the ShopCode and Product has same values but with different value from Qty field, the output will not be summed hence I am getting double entries/record.

My script is:

SHOP:

LOAD DISTINCT
             ShopCode
FROM []
(qvd);

For i=0 to NoOfRows('SHOP')-1

Let vShop = Peek('ShopCode', i , 'SHOP');

1_SHOP:

LOAD
           ShopCode
           Product
           sum(Qty) as S_Qty
FROM []
(qvd)
where ShopCode = '$(vShop)' ;
Group by
               ShopCode
               Product
               Qty;

Store 1_SHOP into [$(vShop).csv] (txt);
Drop table 1_SHOP;

Next i

Here is the sample table:

ShopCode, Product, Qty

1A, XYZ, 10

1B, ZVX, 20,

1C, TYW, 30

1A, XYZ, 10

1A, XYZ, 30

This is the output I'm getting:

ShopCode, Product, Qty

1A, XYZ, 20

1A, XYZ, 30

1B, ZBX, 20

1C, TYW, 30

double entries for the 1A, XYZ. The value for that field with same Qty is summed but the other one with different value is not added.

The output should be :

ShopCode, Product, Qty

1A, XYZ, 50

1B, ZBX, 20

1C, TYW, 30

[RsQK · Accepted solution]

Hi, not quite sure why you would want to loop through every shop value, but here's a code that will work for you (might need to adjust it a little bit since I'm using resident):

temp:
LOAD *, ROWNO() AS Row INLINE [
ShopCode, Product, Qty
1A, XYZ, 10
1B, ZVX, 20,
1C, TYW, 30
1A, XYZ, 10
1A, XYZ, 30
];

SHOP:
LOAD
FIELDVALUE('ShopCode',RECNO()) AS ShopCode
AUTOGENERATE FIELDVALUECOUNT('ShopCode');

result:
LOAD 0 AS temp_field
AUTOGENERATE 0;

FOR i=0 TO NOOFROWS('SHOP')-1

LET vShop = PEEK('ShopCode',$(i),'SHOP');
TRACE vShop = $(vShop);

CONCATENATE (result)
LOAD
ShopCode,
Product,
SUM(Qty) as Qty
RESIDENT temp
WHERE MATCH(ShopCode,'$(vShop)')
GROUP BY ShopCode,Product;

NEXT;

DROP TABLE temp,SHOP;
DROP FIELD temp_field;

[bryan_21]

Thanks! Finally figured it out, modified some though.