need help in pivot/unpivot calculation

[musketeers]

Hi, I have data like this:

product	Quality1	Quality2	Quality3	Quality4	Quality5	Quality6	Quality7
P1	1	0	1	0	0	1	0
P2	1	0	1	0	0	1	0
P3	1	1	1	0	0	1	1
P4	1	0	1	0	0	1	1
P5	1	0	1	0	1	0	1
P6	1	0	1	1	1	0	1
P7	1	0	1	1	1	0	1
P8	1	1	1	1	1	0	1
P9	0	1	0	1	1	0	1
P10	0	1	0	1	1	0	1
P11	0	1	0	1	1	0	1
P12	0	1	0	1	0	0	1
P13	0	1	1	1	0	1	0
P14	0	1	1	0	0	1	0
P15	1	1	1	0	0	1	0
P16	1	1	1	0	0	1	0
P17	1	0	1	0	0	1	0
P18	1	0	1	0	0	1	0
P19	1	0	1	1	0	1	0
P20	1	0	1	1	0	1	0

The number of products can increase or decrease in next load. Now regardless of client, I need to display the Quality descriptions in following way in straight table:

Quality	Total Count	Total - 1	Total - 0
Quality1	20	14	6
Quality2	20	10	10
Quality3	20	16	4
Quality4	20	10	10
Quality5	20	7	13
Quality6	20	12	8
Quality7	20	10	10

Is there any way we can do this.

Any help is much appreciated.

[marcus_sommer]

Have a look on the crosstable-statement which transformed your crosstable in a data-stream and then is a table-chart in qv very easy.

- Marcus

[anbu1984]

Check this app. Attached app has ValueList for Quality1 and Quality2. You can add for remaining quality

[JonnyPoole]

Doing a 'crosstable' load will structure the table perfectly.

Use this in the load script:

CrossTable(Quality, Count)

LOAD product,

    Quality1,

    Quality2,

    Quality3,

    Quality4,

    Quality5,

    Quality6,

    Quality7

FROM

[http://community.qlik.com/thread/136432]

(html, codepage is 1252, embedded labels, table is @1);

And then a chart with 'Quality' as dimension and these expressions:

1: sum( if(Count=1,1))

0:  sum( if(Count=0,1))

Total:  sum( if(Count=0 or Count=1,1))

[jyothish8807]

Hi Rajneesh,

PFA.

Regards

KC