group data in a column

[lorenzo_gibbo]

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

[Miguel_Angel_Baeyens]

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.

[lorenzo_gibbo]

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

[Miguel_Angel_Baeyens]

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.

[lorenzo_gibbo]

Hi Miguel,

first of all thanks for your answer. I tried with your script, but something is not working properly.

how do you see for each code, shows the total of each name, instead of returning the correct data.

I have done something wrong?

Thanks for your patience

[Miguel_Angel_Baeyens]

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.

[lorenzo_gibbo]

Exact!

Sorry, perhaps I had not explained well.

QTY1 for CODE1

QTY2 for CODE2

.....

Bye

[llauses243]

Hi Gibbo,

This is my offer ... see image & .rar

Good luck, Luis