topic Re: how to solve the pivot table problem in QlikView

how to solve the pivot table problem

Sun, 14 Sep 2014 01:30:57 GMT

Hi,

I have some sample data in pivot table is given below

month	status	grandtotal
jan	lst	40
	nd	85
	wn	125
FEB	lst	36
	nd	87
	wn	113

i need in given below format

month	lst	nd	wn	grandtotal
jan	40	30	3	73
feb	45	35	4	84
mar	65	45	5	115
grandtotal	150	110	12	272

any one please help me..................

Re: how to solve the pivot table problem

JonnyPoole — Sun, 14 Sep 2014 02:05:45 GMT

Here is my solution:

Data:

month	status	grandtotal
JAN	lst	40
	nd	85
	wn	125
FEB	lst	36
	nd	87
	wn	113

Load Script:

Months:

load Distinct

rowno() as Record,

month

FROM

(ooxml, embedded labels, table is Sheet1)

where isnull(month) <> - 1;

left join (Months)

load

ceil(RecNo()/3) as Record,

status,

grandtotal

FROM

(ooxml, embedded labels, table is Sheet1);

Screenshot:

Re: how to solve the pivot table problem

Sun, 14 Sep 2014 02:27:09 GMT

Thnak you so much but i need

the pivot table is generated in sheet level(in dashboard)

month	status	grandtotal
jan	lost	40
	nodecision	85
	won	125
FEB	lost	36
	nodecision	87
	won	113

like this.

after change the data in the given format i required

month	lost	nodecision	won	grandtotal
jan	40	30	3	73
feb	45	35	4	84
mar	65	45	5	115
grandtotal	150	110	12	272

Re: how to solve the pivot table problem

Sun, 14 Sep 2014 02:29:36 GMT

how to write scripting after generating the pivot table in dashboard is it possible plz tell me......

Re: Re: how to solve the pivot table problem

JonnyPoole — Sun, 14 Sep 2014 03:00:41 GMT

You can't reverse engineer a pivot table into script.

If your months are reported on the 'middle row' of each block like this:

month	status	grandtotal
	lst	40
jan	nd	85
	wn	125
	lst	36
feb	nd	87
	wn	113

then tweak the script slightly to this:

Months:

load Distinct

ceil(recno()/3) as Record,

month

FROM

(ooxml, embedded labels, table is Sheet1)

where isnull(month) <> - 1;

left join (Months)

load

ceil(RecNo()/3) as Record,

status,

grandtotal

FROM

(ooxml, embedded labels, table is Sheet1);

Re: how to solve the pivot table problem

Sun, 14 Sep 2014 08:38:22 GMT

sorry jonathan can't understand.i m taking data from .qvd file. the .qvd file loaded into qlikview i need to generate the pivot table. i generate pivot. the pivot table is like this.

month	status	grandtotal
jan	lost	40
	nodecision	85
	won	125
FEB	lost	36
	nodecision	87
	won	113

the grand total column generated after created pivot table not their in before.

but my required pivot table is

month	lost	nodecision	won	grandtotal
jan	40	30	3	73
feb	45	35	4	84
mar	65	45	5	115
grandtotal	150	110	12	272

how plz tell me.....

Re: how to solve the pivot table problem

JonnyPoole — Sun, 14 Sep 2014 14:30:41 GMT

drag the 'status' field to the columns as below. Then go to the chart properties / presentation tab, select month and enable 'partial sums' and repeat for status as well. Please note that the math in your desired output does not reflect the data ... you need to explain why if the totals in the desired output are indeed correct.

Re: how to solve the pivot table problem

Mon, 15 Sep 2014 04:29:02 GMT

k Thanks Jonathan in that I need one more column like % column means won/total=%(column). how to add the column to pivot table plz explain me....

Re: how to solve the pivot table problem

JonnyPoole — Mon, 15 Sep 2014 12:38:49 GMT

You can add a 2nd expression on the expression tab with this formula:

= sum( grandtotal) / sum( total grandtotal)

However this will add a % of total for all status'. Give it a try and see if you like it. If you need it to be more specific then read on:

To only have '% Won' is a little more involved. One way is as follows:

1. Create a new dimension based on status that adds '% Won' as a status

2. Alter the expression to calculate a % of total won for the '% Won' column

Here is a sample load script with 3 steps. On the chart, replace 'status' with 'statusforchart' as the dimension, change the sort order of statusforchart to be 'expression and select 'statussort'. then alter the expression to:

if (statusforchart='% Won' , num( sum( {$<status={wn}>} grandtotal) / sum( {$<status={wn}>} total grandtotal),'#,###.0%') ,num( sum(grandtotal),'#,###,####'))

LOAD SCRIPT:

Months:

LOAD month,

status,

grandtotal

FROM

(ooxml, embedded labels, table is Sheet1);

Status:

load Distinct

status,

status as statusforchart,

if (status = 'lost',1, if(status = 'nodecision', 2 , if (status = 'wn',3))) as statussort

Resident Months;

Concatenate (Status)

load

'wn' as status,

'% Won' as statusforchart,

4 as statussort

AutoGenerate 1;

Re: how to solve the pivot table problem

Tue, 16 Sep 2014 02:59:41 GMT

Hi Jonathan,

Actually my problem is i have .qvd file data. data the used to generate the pivot table like this

month	lost	nodecision	won	grandtotal
jan	40	30	3	73
feb	45	35	4	84
mar	65	45	5	115
grandtotal	150	110	12	272

but i need this type

month	lost	nodecision	won	grandtotal	Rate%
jan	40	30	3	73	won/Grandtotal
feb	45	35	4	84	won/Grandtotal
mar	65	45	5	115	won/Grandtotal
grandtotal	150	110	12	272	won/Grandtotal

my using the this expression count({<status={'won'}>}[month])/count(status)

it will generate like this but i need only rate% column after the grand total only

month	lost		nodecision		won		grandtotal	Rate%
	grandtotal	Rate%	grandtotal	Rate%	grandtotal	Rate%
jan	40		30		3		73
feb	45		35		4		84
mar	65		45		5		115
grandtotal	150		110		12		272

Re: Re: how to solve the pivot table problem

JonnyPoole — Tue, 16 Sep 2014 03:09:11 GMT

okay. try the attached the. it is the example i mentioned as an alternative above