topic group data in a column in QlikView

group data in a column

lorenzo_gibbo — Fri, 15 Apr 2011 10:37:10 GMT

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

group data in a column

Miguel_Angel_Baeyens — Fri, 15 Apr 2011 10:55:24 GMT

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.

group data in a column

lorenzo_gibbo — Fri, 15 Apr 2011 11:24:05 GMT

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

group data in a column

Miguel_Angel_Baeyens — Fri, 15 Apr 2011 12:16:05 GMT

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.

group data in a column

lorenzo_gibbo — Mon, 18 Apr 2011 17:59:19 GMT

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

group data in a column

Miguel_Angel_Baeyens — Mon, 18 Apr 2011 18:48:34 GMT

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.

group data in a column

lorenzo_gibbo — Mon, 18 Apr 2011 19:06:19 GMT

Exact!

Sorry, perhaps I had not explained well.

QTY1 for CODE1

QTY2 for CODE2

.....

Bye

group data in a column

llauses243 — Tue, 19 Apr 2011 00:19:00 GMT

Hi Gibbo,

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

Good luck, Luis