topic Re: Sum function in a for loop/Group by in App Development

Sum function in a for loop/Group by

bryan_21 — Thu, 27 Oct 2022 07:39:05 GMT

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

Re: Sum function in a for loop/Group by

RsQK — Thu, 27 Oct 2022 07:49:32 GMT

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;

Re: Sum function in a for loop/Group by

bryan_21 — Thu, 27 Oct 2022 10:08:50 GMT

Thanks! Finally figured it out, modified some though.