Group by issues - loading all data

[#1]

Hello to all,

I'm facing some issues regarding the Group By function.

Please find the script in attach.

For some reason, my group by doesn't work, it does not create the 'new table', so when I drop the old one, I loose all the data.

Thanks for your help folks!

Regards,

Marco

[sunny_talwar · Accepted solution]

May be you need to add NoConcatenate

[Group_TEMP]:

NoConcatenate
LOAD

[Local/Indent],
[Company Code Description],
%CORPORATE_CUSTOMER,
%IAC1,
//[Sector Hierarchy],
[Internal External],
%ELIMINATION_ENTITY_GROUP,
%ENTITY_GROUP,
[Entity Group],
%ENTITY,
[MO Hierarchy 1],
%MATERIAL_ORIGIN,
[MO Hierarchy 2],
[PFR/Manufactured],
%DESTINATION,
%SALES_UNIT,
Region,
%SECTOR,
YearMonthNum,
IAC1,
%SEC_SHIP_TO_COUNTRY,
//Local Currency],
[Region (External Reporting)],
[Market Sector Description],
//[Product Group Code],
//[Product Group Description],
//[Local and International flag],
//[Ship to Country Name],
//MainShiptoCountryName,
//[Corp/Non Corp Customer],
//[Resp Sales Unit],
[Product Category],
[Sales Rep Code],
[Sales Rep],
//[Material Origin Description],
[Product Hierarchy Description],
//[IAC1 Description],
[Sales Group Description],
[Sales Office Description],
[Sales Org],
[Sales Org Description],
//[Customer Type Description],
[Customer Code],
//MainCustomerCode,
//Customer,
//Company,
//Material,
MaterialKEY,
//[Material Description],
//[%Elimination Company],
[Product Type],
[Ship to Country],
//STC,
[Material Origin],
//[Corp/Non Corp Customer Code],
//CorpCustomerCode,
[Sales Group],
ForecastKey,
MainKey,

//Budget fields:
BudgetShiptoCountryName,
[BudgetCustHierarchyCode],
[BudgetSalesGroup],
[BudgetSalesGroupCode],
[BudgetSalesRepName],
[BudgetSalesRepCode],


SUM([Revenue CC]) as [Revenue CC],
SUM([Open Order Value CC])  as [Open Order Value CC], 
SUM([Delivery Value CC]) as [Delivery Value CC],
SUM([Delivery Value EUR]) as [Delivery Value EUR],
SUM([Delivery Value GBP]) as [Delivery Value GBP],
SUM([Delivery Value USD]) as [Delivery Value USD],
SUM([Delivery Quantity]) as [Delivery Quantity],
SUM([Open Order Value USD]) as [Open Order Value USD],
SUM([Revenue USD]) as [Revenue USD],
SUM([Open Order Value GBP]) as [Open Order Value GBP],
SUM([Revenue GBP]) as [Revenue GBP],
SUM([Open Order Value EUR]) as [Open Order Value EUR],
SUM([Revenue EUR]) as [Revenue EUR],
SUM([Open Order Quantity]) as [Open Order Quantity],
SUM(Volume) as Volume,

//budget fields:
SUM([Budget Revenue (GBP)]) as [Budget Revenue (GBP)],
SUM([Budget Volume (Kg)])  as [Budget Volume (Kg)], 
SUM([Budget Revenue (USD)]) as [Budget Revenue (USD)]


Resident [Main Data]


Group by
[Local/Indent],
[Company Code Description],
%CORPORATE_CUSTOMER,
%IAC1,
//[Sector Hierarchy],
[Internal External],
%ELIMINATION_ENTITY_GROUP,
%ENTITY_GROUP,
[Entity Group],
%ENTITY,
[MO Hierarchy 1],
%MATERIAL_ORIGIN,
[MO Hierarchy 2],
[PFR/Manufactured],
%DESTINATION,
%SALES_UNIT,
Region,
%SECTOR,
YearMonthNum,
IAC1,
%SEC_SHIP_TO_COUNTRY,
//[Local Currency],
[Region (External Reporting)],
[Market Sector Description],
//[Product Group Code],
//[Product Group Description],
//[Local and International flag],
//[Ship to Country Name],
//MainShiptoCountryName,
//[Corp/Non Corp Customer],
//[Resp Sales Unit],
[Product Category],
[Sales Rep Code],
[Sales Rep],
//[Material Origin Description],
[Product Hierarchy Description],
//[IAC1 Description],
[Sales Group Description],
[Sales Office Description],
[Sales Org],
[Sales Org Description],
//[Customer Type Description],
[Customer Code],
//MainCustomerCode,
//Customer,
//Company,
//Material,
MaterialKEY,
//[Material Description],
//[%Elimination Company],
[Product Type],
[Ship to Country],
//STC,
[Material Origin],
//[Corp/Non Corp Customer Code],
//CorpCustomerCode,
[Sales Group],
ForecastKey,
MainKey,

//Budget fields:
BudgetShiptoCountryName,
[BudgetCustHierarchyCode],
[BudgetSalesGroup],
[BudgetSalesGroupCode],                                     
[BudgetSalesRepName],
[BudgetSalesRepCode]
;

DROP TABLE [Main Data];


EXIT SCRIPT;

[#3]

Many thanks Sunny T.

Now I realized that my data has lines in which all the 'dimensions' are zero.

What is the best way to solve it?

If I use where Volume <> '0' or [Open Order Quantity] <> '0'......

For all my dimensions, in my first loading, would that work?

Kind regards,

Marco

[sunny_talwar]

I would think that it should work. Have you tried it? or may be Volume <> 0 or [Open Order Quantity] <> 0; without the single quotes.

[#5]

It worked.

Thanks a lot Sunny T.

[Anonymous]

to avoid autoconcatenation use noconcatenate as suugested by sunny

[sunny_talwar]

Great, please close the thread by marking correct and helpful answers