Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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)
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
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;
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
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
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.