topic Re: Not easy ! How to do a complex pivot table ?? in QlikView

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

zarmoton — Wed, 18 Sep 2013 09:27:49 GMT

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)

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

Sokkorn — Wed, 18 Sep 2013 09:44:09 GMT

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, 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

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

Gysbert_Wassenaar — Wed, 18 Sep 2013 09:46:55 GMT

See attached qvw.

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

orital81 — Wed, 18 Sep 2013 09:47:54 GMT

Separate to 2 tables and use concatenation.

See attached example

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

zarmoton — Wed, 18 Sep 2013 09:48:52 GMT

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

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

zarmoton — Wed, 18 Sep 2013 10:10:28 GMT

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

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

zarmoton — Wed, 18 Sep 2013 10:10:28 GMT

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

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

Wed, 18 Sep 2013 10:11:49 GMT

Hi,

PFA

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

Gysbert_Wassenaar — Wed, 18 Sep 2013 10:16:18 GMT

Look again. You missed the second pivot table.

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

zarmoton — Wed, 18 Sep 2013 10:16:28 GMT

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

100

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

zarmoton — Wed, 18 Sep 2013 10:23:40 GMT

First thk yu

No, I don’t miss the second table

Your solution is ok for a small table like that, but not enough generic

But now imagine a table with 10 millions of rows, and not with 4 names (A, B C D) but with 5000 names !!!

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

Gysbert_Wassenaar — Wed, 18 Sep 2013 10:25:32 GMT

And that's exactly why you need to fix your data model.

Or compromise and use both fields as dimensions in the pivot table. The totals of the rows and columns give you the buyer and seller totals per name.

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

zarmoton — Wed, 18 Sep 2013 11:59:40 GMT

I’m not at all beginner under qlikview ..

My data model is just very very complex, I have more than 200 tables, application is very huge

I cannot change the model so easily, impact is tremendous!!

I really need to find an answer with the pivot table chart object plug directly on a such data model.