Skip to main content
Announcements
The New Qlik Learning Experience is Here! GET STARTED
cancel
Showing results for 
Search instead for 
Did you mean: 
leale1997
Contributor III

Sum by Group

I am trying to sum a data by group so that it doesn't count duplicate records.

In the above data sample I would like it to sum the values based on both the "Description" field and the "ID" field.  So it will count multiple values when the ID is the same but the Description is different.

1 Solution

Accepted Solutions
dinuwanbr
Creator III

Hi Erika,


Try this


LOAD
Delivery,
Material,
Description,
Batch as PCMBatch,
Sum("Delivery quantity") as PCMDeliveryQty,
SU,
"Qty (stckpg unit)",
BUn,

"Delivery date",
if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')) as New_DatePCM,

If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,
if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10,
Sum("Qty (stckpg unit)"))) as Qty2
Group by Description, Batch, Delivery, Material, SU, "Qty (stckpg unit)", BUn,"Delivery date",if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')),If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10;


you need to use all the fields except aggregation fields (like sum, avg, max, etc) in your group by clause


rgds,

Tharindu

View solution in original post

7 Replies
effinty2112
Master

Hi Erika,

A straight table with dimensions Description and ID with expression Sum(Qty) will give you what you need. If you want a script solution then something like:

LOAD

Description,

ID,

Sum(Qty) as Qty

From .....

Group by Description,ID;

should work.

Regards

Andrew

leale1997
Contributor III
Author

So I am trying to use the script below.... Not working.... any ideas?

LOAD
Delivery,
Material,
Description,
Batch as PCMBatch,
Sum("Delivery quantity") as PCMDeliveryQty,
SU,
"Qty (stckpg unit)",
BUn,

"Delivery date",
if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')) as New_DatePCM,

If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,
if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10,
Sum("Qty (stckpg unit)"))) as Qty2
Group by Description,Batch;


SQL SELECT *
FROM
"DEV_SI_Data".dbo."SAP_Distribution_4" WHERE Year("Delivery date")> 2013 ;

Anil_Babu_Samineni

Not working means throwing any error?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
leale1997
Contributor III
Author

When I use the code with no 'sum' fields:

LOAD Delivery,
Material,
Description,
Batch as PCMBatch,
"Delivery quantity" as PCMDeliveryQty,
SU,
"Qty (stckpg unit)",
BUn,
"Delivery date",
if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')) as Format_DD,
Date# ("Delivery date") as ConvertDateNumber,
If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,
if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10,
"Qty (stckpg unit)")) as Qty2

Group by Description,Batch;

When I put in the 'sum' code the table wont connect.

LOAD
Delivery,
Material,
Description,
Batch as PCMBatch,
Sum("Delivery quantity") as PCMDeliveryQty,
SU,
"Qty (stckpg unit)",
BUn,

"Delivery date",
if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')) as New_DatePCM,

If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,
if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10,
Sum("Qty (stckpg unit)"))) as Qty2
Group by Description,Batch;


SQL SELECT *
FROM
"DEV_SI_Data".dbo."SAP_Distribution_4" WHERE Year("Delivery date")> 2013 ;

Anil_Babu_Samineni

You need to aggregate using all non aggregate fields with in Group By..

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
dinuwanbr
Creator III

Hi Erika,


Try this


LOAD
Delivery,
Material,
Description,
Batch as PCMBatch,
Sum("Delivery quantity") as PCMDeliveryQty,
SU,
"Qty (stckpg unit)",
BUn,

"Delivery date",
if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')) as New_DatePCM,

If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,
if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10,
Sum("Qty (stckpg unit)"))) as Qty2
Group by Description, Batch, Delivery, Material, SU, "Qty (stckpg unit)", BUn,"Delivery date",if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')),If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10;


you need to use all the fields except aggregation fields (like sum, avg, max, etc) in your group by clause


rgds,

Tharindu

andrei_delta
Partner - Creator III

hello,

it's not working because the group by clause accepts only aggregation like the others said (max, sum, min, avg etc.), another issue is with the fields that you are making the grouping for instance:

you made the group by like this: "Group by Description,Batch;"

so the script should be looking like this:

LOAD
Description,
Batch as PCMBatch,
Sum("Delivery quantity") as PCMDeliveryQty,
if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')) as New_DatePCM

If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,
if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10,
Sum("Qty (stckpg unit)"))) as Qty2

Resident X*

Group by Description,Batch;


X* being the table you are loading data from.


And i don;t know if you want to make the new field based on the group by you are using, if not it shouldn't be there at all.


Hope it helps

Andrei