Concate two dimensions

[#1]

Hello,

I have two dimensions 1.Customer and 2 Region, when Region is a summary of customers by the region.

I would like to create pivot table or straight table that concatinate these two dimansions in one and have their sales summary.

For example I have Canada, USA, China and Japan as Customers and America and Asia as Region

so my table should be:

Cust+Region     Sales

Canada               10

USA                     10

China                   20

Japan                   20 

America               20

Asia                      40

Total                     60

Attached file as example as well

[SunilChauhan]

Master:

Load  *, Customer&Region as cutomerRegion;

LOAD *,

if(SalesRep = 'B01','Hospitals',

if(SalesRep = 'B02','Mass_Merch',

if(SalesRep = 'B03','China',

if(SalesRep = 'B04','International','Others')))) as Customer,

if(match(SalesRep,'B01','B02'),'US',

if(match(SalesRep,'B03','B04'),'World','Others')) as Region

FROM

Testqv.xlsx

(ooxml, embedded labels, table is Sheet1);

[SergeyMak]

I think you need additional field and do concatenate.

[tmp]:

LOAD *,

Region AS SumRegion

Resident Master;

concatenate (tmp)

LOAD *,

Customer AS SumRegion

Resident Master;

Probably you should filter every load by values in Region and Customer like

First one: WHERE Customer<>"Hostpitals"

Seconde one: WHERE Region<>"World"

, because in other way you will have doubled Sum(TOTAL Sales)

[#4]

Hi Sunil,

Thank you for a responce.

Tha is not exactly what I need.

I need  table with results as following:

CustomerRegion    Sum(qty)

Hospitals                 10

Mass_Merch            15 

China                       20

International             25

USA                         25  (Hospitals + MassMerch)

World                       42  (China + International)

Total                         67 (all customers)

Thank you

[#5]

To build on Sergeys reply, you would do GROUP BY

[tmptable]:
LOAD

Region AS SumRegion, SUM(Qty)

Resident Master

Group By Region;

concatenate [tmptable]

LOAD

Customer AS SumRegion, SUM(Qty)

Resident Master

Group By Customer;

[#6]

Hello Sergey and Colin,

Thank you for yours suggestions.

It works for that specific file, but when I am trying to do it with more complecated script it is crashing.

Nevermind I will try to figure out what is a problem.

Is it possible to do it not in script, but as calculated dimenssion for example?

Thank you

[#7]

Ok It works now.

I just added specific key to join master and tmp table.

Without the key the the script is crashing due to large amount of data.

However I am still wondering if there is a way to do it not on script level?

Thank you

[SergeyMak]

Hi Denis,

Denis Lomakin wrote:

However I am still wondering if there is a way to do it not on script level?

I don't think so.