topic Re: Concate two dimensions in QlikView

Concate two dimensions

Wed, 07 May 2014 02:54:08 GMT

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

Re: Concate two dimensions

SunilChauhan — Wed, 07 May 2014 03:05:44 GMT

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);

Re: Concate two dimensions

SergeyMak — Wed, 07 May 2014 03:17:12 GMT

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)

Re: Concate two dimensions

Wed, 07 May 2014 03:20:01 GMT

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

Re: Concate two dimensions

Wed, 07 May 2014 09:41:35 GMT

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;

Re: Concate two dimensions

Wed, 07 May 2014 16:47:56 GMT

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

Re: Concate two dimensions

Wed, 07 May 2014 20:52:50 GMT

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

Re: Concate two dimensions

SergeyMak — Thu, 08 May 2014 02:09:39 GMT

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.