topic Re: Problem with Sum / Group By in script in QlikView

Problem with Sum / Group By in script

Tue, 15 Sep 2015 09:39:46 GMT

Good morning,

what is wrong with this script code? I get error message

"ODBC read failed

sql select * from GISdb.dbo."SAP_SO_Report" where CD = 'D' and Year > 2013

invalid expression"

By the way, it works if I drop the line " if (Area = 'ACTYTD', TotalCost,0) as Actuals," and the "group by"' clause

Thanks in advance, Leonardo

ODBC connect to GISDB;

load

Area as ValueType,
CD as [Credit / Debit],
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,
ServiceGroup,

TotalCost,
TotalQty,

if (Area = 'ACTYTD', TotalCost,0) as Actuals,
0 as Forecast,
0 as Plan,

if (Area = 'ACTYTD', TotalQty, 0) as [Actuals (Quantity)],
0 as [Forecast (Quantity)],
0 as [Plan (Quantity)],

if (Area = 'ACTYTD', sum (TotalCost),0) as ActualYTD

group by Area, SenderKey, ReceiverKey, Year;

sql select * from GISdb.dbo."SAP_SO_Report" where CD = 'D' and Year > 2013;

Re: Problem with Sum / Group By in script

sunny_talwar — Tue, 15 Sep 2015 09:44:20 GMT

May be this:

Table:

LOAD Area as ValueType,
CD as [Credit / Debit],
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,
ServiceGroup,
TotalCost,
TotalQty,
If (Area = 'ACTYTD', TotalCost,0) as Actuals,
0 as Forecast,
0 as Plan,
If (Area = 'ACTYTD', TotalQty, 0) as [Actuals (Quantity)],
0 as [Forecast (Quantity)],
0 as [Plan (Quantity)];
SQL select * from GISdb.dbo."SAP_SO_Report" where CD = 'D' and Year > 2013;

Join(Table)

LOAD Area,

SenderKey,

ReceiverKey,

Year,
If (Area = 'ACTYTD', sum (TotalCost),0) as ActualYTD

Resident Table

Group By Area, SenderKey, ReceiverKey, Year;

Re: Problem with Sum / Group By in script

sasiparupudi1 — Tue, 15 Sep 2015 10:11:32 GMT

t1:

load

Area as ValueType,

CD as [Credit / Debit],

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,

ServiceGroup,

TotalCost,

TotalQty,

if (Area = 'ACTYTD', TotalCost,0) as Actuals,

0 as Forecast,

0 as Plan,

if (Area = 'ACTYTD', TotalQty, 0) as [Actuals (Quantity)],

0 as [Forecast (Quantity)],

0 as [Plan (Quantity)],

if (Area = 'ACTYTD', TotalCost,0) as ActualYTD;

sql select * from GISdb.dbo."SAP_SO_Report" where CD = 'D' and Year > 2013;

no concatenate

final:

load

ValueType,

[Credit / Debit],

Order,

[Order (Long)],

Account,

[Account (Long)],

[Month (Long)],

Year,

Unit,

ServiceGroup,

TotalCost,

TotalQty,

Actuals,

Forecast,

Plan,

[Actuals (Quantity)],

[Forecast (Quantity)],

[Plan (Quantity)],

sum(ActualYTD) as ActualYTD

resident t1

group by

ValueType,

[Credit / Debit],

Order,

[Order (Long)],

Account,

[Account (Long)],

[Month (Long)],

Year,

Unit,

ServiceGroup,

TotalCost,

TotalQty,

Actuals,

Forecast,

Plan,

[Actuals (Quantity)],

[Forecast (Quantity)],

[Plan (Quantity)]

;

drop table t1;

hth

Sasi

Re: Problem with Sum / Group By in script

Wed, 16 Sep 2015 16:45:07 GMT

Good afternoon,

it seems I´m not using the Group By clause correctly... Rather than loading in one steps, I now use the code below, but get an error "Invalid expression" - why? Sort of frustrated because doing sums in the script should be trivial...

Many thanks in advance,

Leonardo

ODBC connect to GISDB;

// ******************** (1) Get all data
WorkTable1:
load
Area as ValueType,
CD as [Credit / Debit],
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,
ServiceGroup,

TotalCost,
TotalQty;

sql select * from GISdb.dbo."SAP_SO_Report" where CD = 'D' and Year > 2013;

// ******************** (2) Sum yearly values

WorkTable2:
noconcatenate load *,

if (ValueType = 'ACTYTD', sum (TotalCost),0) as ActualYTD
//if (ValueType = 'FCYEAR', sum (TotalCost),0) as ForecastYTD,
//if (ValueType = 'PLANYEAR', sum (TotalCost),0) as BudgetYTD,
//if (ValueType = 'ACTYTD', sum (TotalQty),0) as ActualQtyYTD,
//if (ValueType = 'FCYEAR', sum (TotalQty),0) as ForecastQtyYTD,
//if (ValueType = 'PLANYEAR', sum (TotalQty),0) as BudgetQtyYTD
resident WorkTable1
group by ValueType, Order, Account, Year;

Re: Problem with Sum / Group By in script

Wed, 16 Sep 2015 16:46:02 GMT

Thanks Sunny but this results in wrong sums, pls see my latest attempt below.

Many thanks in advance!

Leonardo

Re: Problem with Sum / Group By in script

Wed, 16 Sep 2015 16:47:34 GMT

Thanks Sasidhar. Actually I want to add up across months so I can´t have all the fields including the month in the Group By clause. Pls have a look at my latest attempt below,

Many thanks in advance!

Leonardo

Re: Problem with Sum / Group By in script

maxgro — Wed, 16 Sep 2015 16:55:54 GMT

when you group by all the fields not aggregated must be in the group by

this works

load

sum(....),

count(....)

resident sometable

group by a, b, c;

this doesn't, it returns invalid expression because c field is missing in group by

load

sum(....),

count(....)

resident sometable

group by a, b;

Re: Problem with Sum / Group By in script

Thu, 17 Sep 2015 07:23:27 GMT

Thanks. But then how can I make sums? Lets say I got

Customer

Year

Month

Sales

and I want to calculate the sales per customer and year.

Re: Problem with Sum / Group By in script

maxgro — Thu, 17 Sep 2015 07:46:46 GMT

load

Customer, Year,

sum(Sales)

from

???

group by

Customer, Year;

or in a chart with the same 2 dimensions