topic Re: need help in QlikView

need help

Wed, 25 Nov 2020 16:16:04 GMT

Hi Viewers,

Please have a look at my scenario,I have taken pivot table to accomplish this.

But not getting what exactly am looking for.am getting results in two tables I need to show in one table. As I shown below.

I wanted to confirm whether its possible or not in pivot table

PFB for your reference.

Source Table:

State	Role	hc	act	gap
ap	a1	1	1	0
ap	a2	2	1	1
ap	a3	3	2	1
tel	a1	2	1	1
tel	a2	3	3	0
tel	a3	1	1	0
tam	a1	3	1	2
tam	a2	2	1	1
tam	a3	1	1	0
kar	a1	2	2	0
kar	a2	1	1	0
kar	a3	3	2	1
krl	a1	1	1	0
krl	a2	2	1	1
krl	a3	3	3	0

Expected Output:

Role	a1			a2			a3			Status
Status	hc	act	gap	hc	act	gap	hc	act	gap	hc	act	gap	%
ap	1	1	0	2	1	1	3	2	1	6	4	2	67%
tel	2	1	1	3	3	0	1	1	0	6	5	1	83%
tam	3	1	2	2	1	1	1	1	0	6	3	3	83%
kar	2	2	0	1	1	0	3	2	1	6	5	1	50%
krl	1	1	0	2	1	1	3	3	0	6	5	1	83%
Total	9	6	3	10	7	3	11	9	2	30	22	8	74%

Please suggest me on this.

Thanks in adv!

Re: need help

sunny_talwar — Tue, 21 Mar 2017 12:54:09 GMT

This?

Re: need help

sunny_talwar — Tue, 21 Mar 2017 12:58:35 GMT

With Total at the bottom

Re: need help

Tue, 21 Mar 2017 12:59:44 GMT

Yes Sunny almost reached.

But how to show totals in below as I shown above.

Please have a look at.

And also please share your script here as am not able to open your file.

Re: need help

sunny_talwar — Tue, 21 Mar 2017 13:05:54 GMT

Script

Table:

LOAD * INLINE [

State, Role, hc, act, gap

ap, a1, 1, 1, 0

ap, a2, 2, 1, 1

ap, a3, 3, 2, 1

tel, a1, 2, 1, 1

tel, a2, 3, 3, 0

tel, a3, 1, 1, 0

tam, a1, 3, 1, 2

tam, a2, 2, 1, 1

tam, a3, 1, 1, 0

kar, a1, 2, 2, 0

kar, a2, 1, 1, 0

kar, a3, 3, 2, 1

krl, a1, 1, 1, 0

krl, a2, 2, 1, 1

krl, a3, 3, 3, 0

];

Dim1:

LOAD * Inline [

Dim1

];

Dim2:

LOAD * Inline [

Dim2

];

Pivot table

Dimensions

State

=Pick(Dim1, Role, 'Total')

=Pick(Dim2, 'hc', 'act', 'gap', '%')

Expression

=Pick(Dim2, Sum(hc), Sum(act), Sum(gap), If(Dim1 = 2, Num(Sum(act)/Sum(hc), '##%')))

Also, enable 'Show Partial Sums' for State dimension from presentation tab

Re: need help

Tue, 21 Mar 2017 17:28:33 GMT

Sunny, Thanks for your quick response.

Just now I tried as you suggested.

Created Inline tables with dim1(1,2) and another table dim2(1,2,3,4)

Now I have taken my dimensions as

state, and in calculated dimension

=Pick(Dim1, Role, 'Total')

=Pick(Dim2, 'hc', 'act', 'gap', '%')

and Expression

=Pick(Dim2, Sum(hc), Sum(act), Sum(gap), If(Dim1 = 2, Num(Sum(act)/Sum(hc), '##%')))

Then why am not getting results as same like you.Please suggest where did am wrong.

Pls help!

Re: need help

sunny_talwar — Tue, 21 Mar 2017 17:30:26 GMT

What are you getting? May be check 'Always Fully Expanded' on the presentation tab

Re: need help

Tue, 21 Mar 2017 18:00:19 GMT

Hey Sunny thank you so much for your solution really its working great now.!!!

Thanks again!!!