Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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
See attached qvw.
Separate to 2 tables and use concatenation.
See attached example
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
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
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
Hi,
PFA
Look again. You missed the second pivot table.
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