topic Re: need help in pivot/unpivot calculation in QlikView

need help in pivot/unpivot calculation

musketeers — Sat, 04 Oct 2014 17:10:11 GMT

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.

Re: need help in pivot/unpivot calculation

marcus_sommer — Sat, 04 Oct 2014 17:29:17 GMT

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

Re: need help in pivot/unpivot calculation

anbu1984 — Sat, 04 Oct 2014 17:29:53 GMT

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

Re: need help in pivot/unpivot calculation

JonnyPoole — Sat, 04 Oct 2014 17:30:43 GMT

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

Re: need help in pivot/unpivot calculation

jyothish8807 — Sat, 04 Oct 2014 17:44:27 GMT

Hi Rajneesh,

PFA.

Regards