pivot table

[nareshthavidishetty]

Hi,

I need to develop a pivot table by combining two pivot tables which has different dimensions.Please check the below images and attached qvw  for your reference.I want table like 3rd image(I just crop the 3rd image in paint)

[mithilesh17]

Hi,

Concatenate both the table in script and try

try below code

PRODUCT_SALES:
LOAD Product,
     STATE,
     SALES1
FROM

(ooxml, embedded labels, table is Sheet1);

concatenate

LOAD Product1 as Product,
     state as STATE,
     sales as SALES1
FROM

(ooxml, embedded labels, table is Sheet2);

Then create Pivot table with

Product and STATE as dimension and

Sum(SALES1) as expression

Regards

[nareshthavidishetty]

But there i need to write different expression for sales and Sales1

[mithilesh17]

Hi,

then use outer join

like

PRODUCT_SALES:
LOAD Product,
     STATE,
     SALES1
FROM

(ooxml, embedded labels, table is Sheet1);

JOIN

LOAD Product1 as Product,
     state as STATE,
     sales
FROM

(ooxml, embedded labels, table is Sheet2);

and

Then create Pivot table with

Product and STATE as dimension and

Sum(if(isnull(SALES1) or len(trim(SALES1))=0,sales,SALES1) as expression

Regards

[ecolomer]

In order to have unified both files, use concatenate function and the result are a file with the data from 2 tables