Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need help for this problem.
I have this table1 where each name are associated with different codes:
NAME | CODE1 | CODE2 | CODE3 | CODE4 |
Red | A | B | C | |
Blue | B | C | D | E |
Grey | A | B | C | |
Green | C | D |
then I have this table2 where each name is associated with an amount for each code
NAME | QTY1 | QTY2 | QTY3 | QTY4 |
Red | 2 | 5 | 3 | |
Blue | - | 3 | 4 | 2 |
Grey | 1 | 1 | 2 | |
Green | 3 | 5 |
I recall the charts with this two expressions:
SQL SELECT
NAME,CODE1,CODE2,CODE3,CODE4
FROM table1;
SQL SELECT
NAME,QTY1,QTY2,QTY3,QTY4
FROM table2;
The table that I would get is like this:
NAME | CODE | A | B | C | D | E |
TOT_QTY | 3 | 6 | 11 | 9 | 2 | |
Red | 2 | 5 | 3 | |||
Blue | - | 3 | 4 | 2 | ||
Grey | 1 | 1 | 2 | |||
Green | 3 | 5 |
where there is a dimension called CODE which includes all the codes and a dimensione called TOT_QTY that include all the quantity for each name/code.
I hope that I explained.
Can someone help me?
Thanks to all
Hello,
According to your script, something like the following should work
Table1: // Loading from INLINE, SELECT would do as wellCROSSTABLE (CODENAME, CODE) LOAD * INLINE [NAME, CODE1, CODE2, CODE3, CODE4Red, A, B, CBlue, B, C, D, EGrey, , A, B, CGreen, , C, D]; Table2:CROSSTABLE (QTYNAME, QTY) LOAD * INLINE [NAME, QTY1, QTY2, QTY3, QTY4Red, 2, 5, 3Blue, , 3, 4, 2Grey, , 1, 1, 2Green, , 3, 5]; DROP FIELDS CODENAME, QTYNAME;
If you now create a Pivot table where NAME and CODE are dimensions and SUM(QTY) is the expressions, go to the chart properties, Presentation, select NAME and check "Show Partial Sums", check "Subtotals on top", check "Always Fully Expanded" and with the mouse click and hold the CODE field until you see a blue arrow and drag it to the right top (so the arrow changes from vertical to horizontal), the release.
Hope that helps.
Hello Miguel, thank you for your reply.
Sorry, but I don't understand. In my example I have put some value, but in reality I don't know them. How can I put them into the script? The are many more CODE and NAME and therefore the amount...
Hi,
The basic idea remains. The above is just an example of what you can get using a CROSSTABLE load, and how a CROSSTABLE works. However, it may slow your load process if you are managing a very large data set... Anyway, this is a good idea to transform columns into records. Check this sample file.
Hope that helps.
Hi,
Now I'm missing something: how do you associate QTY to CODEs? Or is that CODE1 means QTY1, CODE2 means QTY2 and so?
Regards.
Exact!
Sorry, perhaps I had not explained well.
QTY1 for CODE1
QTY2 for CODE2
.....
Bye