Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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
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;
Thanks Sunny but this results in wrong sums, pls see my latest attempt below.
Many thanks in advance!
Leonardo
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
when you group by all the fields not aggregated must be in the group by
this works
load
a,
b,
c,
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
a,
b,
c,
sum(....),
count(....)
resident sometable
group by a, b;
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.
load
Customer, Year,
sum(Sales)
from
???
group by
Customer, Year;
or in a chart with the same 2 dimensions