Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
ProductCode | ProductDescription | ProductPrice | ProductUnits | ProductCurrency |
---|---|---|---|---|
AVB | Insurance for Karl | 150 | 1 | SEK |
AVB | Insurance for Hanna | 75 | 1 | SEK |
KON | Bicycle | 500 | 2 | SEK |
I have a Product table with data that are loaded to my app, but now I wanna use the data in the load script for other calculations and need to know how many ProductUnits I have for every ProductCode. I'm trying to something like this but only get invalid expression:
Any help are appreciated
TMPPRODUCT:
NOCONCATENATE
LOAD SUM(ProductUnits) as ProductUnits, ProductCode, ProductDescription, ProductPrice, ProductCurrency RESIDENT Product WHERE MATCH(ProductCode, 'AVB', 'KON', 'KRED') GROUP BY ProductCode;
You must put every field not used in an aggregation function (like sum) into the group by clause.
TMPPRODUCT:
NOCONCATENATE
LOAD SUM(ProductUnits) as ProductUnits, ProductCode, ProductDescription, ProductPrice, ProductCurrency RESIDENT Product WHERE MATCH(ProductCode, 'AVB', 'KON', 'KRED') GROUP BY ProductCode, ProductDescription, ProductPrice, ProductCurrency ;
You must put every field not used in an aggregation function (like sum) into the group by clause.
TMPPRODUCT:
NOCONCATENATE
LOAD SUM(ProductUnits) as ProductUnits, ProductCode, ProductDescription, ProductPrice, ProductCurrency RESIDENT Product WHERE MATCH(ProductCode, 'AVB', 'KON', 'KRED') GROUP BY ProductCode, ProductDescription, ProductPrice, ProductCurrency ;
Hi ,
As Gysbert said you have to include every column in your group by statement.
TMPPRODUCT:
NOCONCATENATE
LOAD SUM(ProductUnits) as ProductUnits,
ProductCode,
ProductDescription,
ProductPrice,
ProductCurrency
RESIDENT Product WHERE MATCH(ProductCode, 'AVB', 'KON', 'KRED')
GROUP BY ProductCode,
ProductDescription,
ProductPrice,
ProductCurrency;
Regards
Prashanth
Use all fields,which is used in load except ProductUnits. i.e..
TMPPRODUCT:
NOCONCATENATE
LOAD
ProductCode,
ProductDescription,
ProductPrice,
ProductCurrency,
SUM(ProductUnits) as ProductUnits
RESIDENT Product WHERE MATCH(ProductCode, 'AVB', 'KON', 'KRED')
GROUP BY
ProductCode,
ProductDescription,
ProductPrice,
ProductCurrency;