topic Re: Syntax for group by in QlikView

Syntax for group by

Fri, 19 Jun 2015 08:33:48 GMT

Good morning,

what is wrong with the syntax below? ForecastTemp1 does contain records,

Thanks in advance,

Leonardo

ForecastTemp1:
SQL select * FROM GISdb.dbo."SAP_SO_Report" where CD = 'D' and Year > 2013 and Area = 'FCYEAR';

ForecastTemp2:
load
Area as ValueType,
CD as [Credit / Debit],
GISLE,

SenderKey as Order,
SenderKey & ' ' & Sender as [Order (Long)],
ReceiverKey as Account,
ReceiverKey & ' ' & Receiver as [Account (Long)],
if (Month > 9, '0' & Month, '00' & Month) as [Month (Long)],
Year,
Unit,
sum (TotalCost) as ForecastTotal,
sum (TotalQty) as ForecastTotalQty,
ServiceGroup
resident ForecastTemp1
group by Area, CD, GISLE, SenderKey, ReceiverKey, Year, Month, ServiceGroup;
drop tables ForecastTemp1;

Re: Syntax for group by

datanibbler — Fri, 19 Jun 2015 08:36:43 GMT

Hi Leonardo,

in a LOAD with an aggregation function, the GROUP BY clause must contain ALL OTHER fields (except the ones where you have some aggregation fct.).

You might not want that - in that case you must not include the fields you do not want to group by in that LOAD, but join them to the aggregate table lateron.

HTH

Re: Syntax for group by

Fri, 19 Jun 2015 08:36:48 GMT

Hi Leonqrdo,

you need to add the non aggregated fields used in your load script to your group by, at the moment 'Sender', 'Receiver' and 'Unit' are missing from the group by

hope that helps

Joe

Re: Syntax for group by

fvelascog72 — Fri, 19 Jun 2015 08:38:20 GMT

Hi,

I think Unit, Sender and Receiver Fields may cause error syntax cause doesn´t have aggregation fucntion.

Re: Syntax for group by

buzzy996 — Fri, 19 Jun 2015 08:48:33 GMT

i think ur if statement causing the issue here,because of grouping.

Re: Syntax for group by

jagan — Fri, 19 Jun 2015 08:52:59 GMT

Hi,

You missed Sender, Receiver, Unit in the Group by clause. Try below statement

ForecastTemp2:
load
Area as ValueType,
CD as [Credit / Debit],
GISLE,

SenderKey as Order,
SenderKey & ' ' & Sender as [Order (Long)],
ReceiverKey as Account,
ReceiverKey & ' ' & Receiver as [Account (Long)],
if (Month > 9, '0' & Month, '00' & Month) as [Month (Long)],
Year,
Unit,
sum (TotalCost) as ForecastTotal,
sum (TotalQty) as ForecastTotalQty,
ServiceGroup
resident ForecastTemp1
group by Area, CD, GISLE, SenderKey, ReceiverKey, Year, Month, ServiceGroup, Sender, Receiver, Unit;
drop tables ForecastTemp1;

Re: Syntax for group by

Peter_Cammaert — Fri, 19 Jun 2015 09:21:33 GMT

Or if you're sure that Sender will be the same if SenderKey is the same and the same logic applies to ReceiverKey/Receiver (it usually does), use this (fewer GROUP BY fields):

ForecastTemp2:
load
Area as ValueType,
CD as [Credit / Debit],
GISLE,

SenderKey as Order,
SenderKey & ' ' & Only(Sender) as [Order (Long)],
ReceiverKey as Account,
ReceiverKey & ' ' & Only(Receiver) as [Account (Long)],
if (Month > 9, '0' & Month, '00' & Month) as [Month (Long)],
Year,
Unit,
sum (TotalCost) as ForecastTotal,
sum (TotalQty) as ForecastTotalQty,
ServiceGroup
resident ForecastTemp1
group by Area, CD, GISLE, SenderKey, ReceiverKey, Year, Month, ServiceGroup, Unit;
drop tables ForecastTemp1;

Re: Syntax for group by

sasiparupudi1 — Fri, 19 Jun 2015 09:31:21 GMT

try

ForecastTemp1:
SQL select * FROM GISdb.dbo."SAP_SO_Report" where CD = 'D' and Year > 2013 and Area = 'FCYEAR';

NoConcatenate