Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
flygstolen_fred
Not applicable

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
Not applicable

Re: Sum & group by in load from resident table

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
3 Replies
Gysbert_Wassenaar
Not applicable

Re: Sum & group by in load from resident table

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
Not applicable

Re: Sum & group by in load from resident table

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
Not applicable

Re: Sum & group by in load from resident table

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;