Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
flygstolen_fred
Creator
Creator

Sum & group by in load from resident table

Hi,

ProductCodeProductDescriptionProductPriceProductUnitsProductCurrency
AVBInsurance for Karl1501SEK
AVBInsurance for Hanna751SEK
KONBicycle5002SEK

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;

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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 ;


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Gysbert_Wassenaar

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 ;


talk is cheap, supply exceeds demand
prashanth1712
Contributor III
Contributor III

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

rammuthiah
Creator III
Creator III

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;