Group by issues - loading all data

Mon, 18 Apr 2016 18:15:53 GMT

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

Re: Group by issues - loading all data

sunny_talwar — Mon, 18 Apr 2016 18:42:28 GMT

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;

Re: Group by issues - loading all data

Mon, 18 Apr 2016 19:29:17 GMT

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

Re: Group by issues - loading all data

sunny_talwar — Mon, 18 Apr 2016 20:06:08 GMT

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.

Re: Group by issues - loading all data

Tue, 19 Apr 2016 11:44:37 GMT

It worked.

Thanks a lot Sunny T.

Re: Group by issues - loading all data

Anonymous — Tue, 19 Apr 2016 11:56:17 GMT

to avoid autoconcatenation use noconcatenate as suugested by sunny

Re: Group by issues - loading all data

sunny_talwar — Tue, 19 Apr 2016 12:21:29 GMT

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

topic Re: Group by issues - loading all data in QlikView

Group by issues - loading all data

Re: Group by issues - loading all data

Re: Group by issues - loading all data

Re: Group by issues - loading all data

Re: Group by issues - loading all data

Re: Group by issues - loading all data

Re: Group by issues - loading all data