Not easy ! How to do a complex pivot table ??

[zarmoton]

Hello

This is a very light sample to explain my problerm, my database is very huge. So i cannot change to format of the input data., and with creating an intermediate other table

Name seller	Amount	Name Buyer
A	20	C
A	40	A
A	100	B
A	40	B
B	10	A
B	10	A
B	30	C
C	1	A
C	2	D

how to build a pivot to give that, directly link of the table as above ???:

Name	Seller	Buyer
A	200	61
B	50	140
C	3	50
D		2
Total	253	253

Thank you for your help

As you can see the column "Seller" is the result of a pivot table of Name Seller / Sum (Amount)

and the colum "Buyer" is the result of a pivot table of Name Buyer/ Sum (Amount)

[Sokkorn]

Hi,

Maybe you can try

[Data]:

LOAD * Inline [

Name seller,    Amount,    Name Buyer

A,    20,    C

A,    40,    A

A,    100,    B

A,    40,    B

B,    10,    A

B,    10,    A

B,    30,    C

C,    1,    A

C,    2,    D];

[Final]:

LOAD

    [Name seller]    AS [Name],

    Sum(Amount)        AS [Seller]

Resident [Data] Group by [Name seller];

[Buyer]:

Join ([Final])

LOAD

    [Name Buyer]    AS [Name],

    Sum(Amount)        AS [Buyer]

Resident [Data] Group by [Name Buyer];

DROP Table [Data];

Regards,

Sokkorn

[Gysbert_Wassenaar]

See attached qvw.

[orital81]

Separate to 2 tables and use concatenation.

See attached example

[zarmoton]

Thank you

But not so easy !

As I explain,

the data model is very very complex (more than 200 tables !!)

I looking for how to create the pivot table object directly link to the table of data. Without creating other intermediate table in the loading script

[zarmoton]

thk , but same answer,

Thank you

But not so easy !

As I explain,

the data model is very very complex (more than 200 tables !!)

I looking for how to create the pivot table object directly link to the table of data. Without creating other intermediate table in the loading script

Thank you

All solution proposed are obvious for this sample data table, but my point is how to create the pivot table chart object himself directly plug of the original table

[zarmoton]

thk , but same answer,

Thank you

But not so easy !

As I explain,

the data model is very very complex (more than 200 tables !!)

I looking for how to create the pivot table object directly link to the table of data. Without creating other intermediate table in the loading script

Thank you

All solution proposed are obvious for this sample data table, but my point is how to create the pivot table chart object himself directly plug of the original table

[#8]

Hi,

PFA

[Gysbert_Wassenaar]

Look again. You missed the second pivot table.

[zarmoton]

Thk you alos for your answer,

But not a good answer for me

All solution proposed are obvious for this sample data table, but my point is how to create the pivot table chart object himself directly plug of the original table

Name seller

Amount

Name Buyer

A

20

C

A

40

A

A

100

B

A

40

B

B

10

A

B

10

A

B

30

C

C

1

A

C

2

D