topic Re: Summing in a Script in QlikView

Summing in a Script

Sat, 05 Sep 2009 02:27:33 GMT

I have a table that has part number, plant location and quantity.

But I want the script to return only Part number and the "total" quantity for both plants.

How do I sum the to quantity field. I get an error is I use Sum(Quantity) as TotalQty.

Stephen

Summing in a Script

Anonymous — Sat, 05 Sep 2009 02:32:17 GMT

Stephen,
Make sure to add GROUP BY:
LOAD
plant,
sum(quantity) as TotalQty
RESIDENT...
GROUP BY plant;

Summing in a Script

Sat, 05 Sep 2009 02:42:29 GMT

Yes, forgot the Group By.

Thanks

Re: Summing in a Script

Wed, 03 Oct 2012 16:53:20 GMT

Mike,

Do you have to use "Group By" when using Sum() in a load script under a resident Load? Im trying to sum up a field prior to my Expression.

PV:

NoConcatenate //Must do this or it will drop all tables at DROP statement

LOAD

//Text(WBS) as EVM_WBS,

Text(WBS) as PV_WBS,

Text([Task Name]) as [PV_Name],

Date(Num#(PV_Weeks),'DD-MMM-YY') as EVM_Weeks,

PV_Amount,

TBC as PV_TBC

Sum(TBC) as BAC

Resident tmpPV;

Thanks!

Brian

Re: Summing in a Script

Gysbert_Wassenaar — Wed, 03 Oct 2012 17:07:17 GMT

Yes, if you use an aggregation function you must define the fields over which you want to aggregate. You do that with the group by statement.

Re: Summing in a Script

Wed, 03 Oct 2012 19:52:53 GMT

ok, thank you gwassenaar. So in my example would I group by: TBC since that's what I'm summing?

Re: Summing in a Script

Gysbert_Wassenaar — Wed, 03 Oct 2012 19:56:03 GMT

Just the exact opposite The fields in the group by statement are all the fields that are not used in an aggregation function. In this case all fields except TBC which gets aggregated in sum(TBC)

Re: Summing in a Script

Wed, 03 Oct 2012 21:09:32 GMT

Ok, glad I asked. You learn something new every day.

Thanks!