topic Re: Pivot table - show first dimension value based on row level condition in QlikView

Pivot table - show first dimension value based on row level condition

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Helo,

I have data like this

LOAD * INLINE [

F1, F2, F3, F4, F5, F6

a1, b1, c1, d1, e1, -2

a1, b1, c1, d1, e2, -2

a1, b1, c1, d1, e3, -3

a2, b2, c2, d2, e2, -24

a3, b3, c3, d3, e1, -10

];

On the sheet, I used a listbox to select F5s that I want to see. I also used an inputbox to specify the sum(F6) values that I want to see.

If i select F5 = e1, e2 and give -2 in the inputbox, the pivot table display below rows.

a1, b1, c1, d1, e1, -2

a1, b1, c1, d1, e2, -2

Question - Is it possible to display just one of that row? That is, if F1, F2, F3, F4 are same and if sum(F6) is also same then just display one of the F5, maybe the first one.

Attached the qvw.

Re: Pivot table - show first dimension value based on row level condition

sunny_talwar — Fri, 13 Jul 2018 14:22:35 GMT

Does this happen only if you select e1, e2? When nothing is selected in F5... would you want to see both the rows?

Re: Pivot table - show first dimension value based on row level condition

Anonymous — Fri, 13 Jul 2018 15:18:22 GMT

No always. Whatever F5 I select, if F1, F2, F3, F4 are same and if sum(F6) is also same then display one of the F5

Re: Pivot table - show first dimension value based on row level condition

sunny_talwar — Fri, 13 Jul 2018 15:24:52 GMT

Try this

IF($(val)=0, Sum(F6), IF(Sum(F6)=$(val), If(RowNo() = 1, $(val)), 0))

Re: Pivot table - show first dimension value based on row level condition

Anonymous — Fri, 13 Jul 2018 16:28:30 GMT

RowNo=1 does not work if the first row is zero. With below data and F5=e0, e1, e2 selected and val= -2, pivot is not showing any data.

LOAD * INLINE [

F1, F2, F3, F4, F5, F6

a1, b1, c1, d1, e0, 0

a1, b1, c1, d1, e1, -2

a1, b1, c1, d1, e2, -2

a1, b1, c1, d1, e3, -3

a2, b2, c2, d2, e2, -24

a3, b3, c3, d3, e1, -10

];

Re: Pivot table - show first dimension value based on row level condition

sunny_talwar — Fri, 13 Jul 2018 16:34:06 GMT

May be give this a shot

IF($(val)=0, Sum(F6), IF(Sum(F6)=$(val), If(MinString(TOTAL <F1, F2, F3, F4> Aggr(IF(Sum(F6)=$(val), F5), F1, F2, F3, F4, F5)) = F5, $(val)), 0))

Re: Pivot table - show first dimension value based on row level condition

Anonymous — Mon, 16 Jul 2018 14:06:46 GMT

This works for the requirement I mentioned. Thanks.

But turns out the $(val) is sum grouped by F1, F5 only not with all 5 dimensions. So with below data, if I select F5=e0, e1, e2 and give val = -5, the pivot should display

a1, b1, c1, d1, e1, -2

a1, b2, c2, d2, e1, -3

LOAD * INLINE [ !

F1, F2, F3, F4, F5, F6

a1, b1, c1, d1, e0, 0

a1, b1, c1, d1, e1, -2

a1, b2, c2, d2, e1, -3

a1, b1, c1, d1, e2, -2

a1, b2, c2, d2, e2, -3

a1, b1, c1, d1, e3, -3

a2, b2, c2, d2, e2, -24

a3, b3, c3, d3, e1, -10

];

I tried changing your expression to this but it is not displaying any record

IF($(val)=0, Sum(F6), IF(Sum(F6)=$(val), If(MinString(TOTAL <F1, F2, F3, F4> Aggr(IF(Sum(Total <F1, F5> F6)=$(val), F5), F1, F2, F3, F4, F5)) = F5, $(val)), 0))

Any idea?

Re: Pivot table - show first dimension value based on row level condition

sunny_talwar — Mon, 16 Jul 2018 14:14:15 GMT

I don't think I understand your requirement here... can you elaborate on the steps you are taking to get to this answer?

Re: Pivot table - show first dimension value based on row level condition

Anonymous — Mon, 16 Jul 2018 14:57:28 GMT

The new data is

LOAD * INLINE [

F1, F2, F3, F4, F5, F6

a1, b1, c1, d1, e0, 0

a1, b1, c1, d1, e1, -2

a1, b2, c2, d2, e1, -3

a1, b1, c1, d1, e2, -2

a1, b2, c2, d2, e2, -3

a1, b1, c1, d1, e3, -3

a2, b2, c2, d2, e2, -24

a3, b3, c3, d3, e1, -10

];

Earlier the value i entered in the inputbox was sum of F6 grouped by F1, F2, F3, F4, F5. Now that is sum of F6 grouped by F1 and F5 only.

So with previous sum requirement, if I give -2, and select F5=e0, e1, e2 I got theses rows

a1, b1, c1, d1, e1, -2

a1, b1, c1, d1, e2, -2

With your spl logic it reduced to

a1, b1, c1, d1, e1, -2

With new sum requirement, if i select F5=e0, e1, e2 and give -5 I would get these rows. Here sum of F6 by F1 and F5 is -5.

a1, b1, c1, d1, e1, -2

a1, b2, c2, d2, e1, -3

a1, b1, c1, d1, e2, -2

a1, b2, c2, d2, e2, -3

With spl logic it should get reduced to

a1, b1, c1, d1, e1, -2

a1, b2, c2, d2, e1, -3

For this, in the spl logic, instead of comparing sum(F6)=$(val) I gave Sum(Total <F1, F5> F6)=$(val) but it doesn't seem to work.

IF($(val)=0, Sum(F6), IF(Sum(F6)=$(val), If(MinString(TOTAL <F1, F2, F3, F4> Aggr(IF(Sum(Total <F1, F5> F6)=$(val), F5), F1, F2, F3, F4, F5)) = F5, $(val)), 0))

The sample i attached in the previous reply has the new data and the logic

Hope I made myself clear. Let me know.

Re: Pivot table - show first dimension value based on row level condition

Anonymous — Mon, 16 Jul 2018 15:04:33 GMT

It works with this logic. Thanks.

IF($(val)=0, Sum(F6), IF(Sum(Total <F1, F5> F6)=$(val), If(MinString(TOTAL <F1, F2, F3, F4> Aggr(IF(Sum(Total <F1, F5> F6)=$(val), F5), F1, F2, F3, F4, F5)) = F5, Sum(F6)), 0))

Re: Pivot table - show first dimension value based on row level condition

sunny_talwar — Mon, 16 Jul 2018 15:15:28 GMT

Nice

Re: Pivot table - show first dimension value based on row level condition

Anonymous — Mon, 16 Jul 2018 16:48:18 GMT

I hate to continue this thread after marking the correct answer. Since we already set the context I am continuing but pls let me know if you need a new thread.

The requirement changed. The value i entered before in the input box are in an island table now.

LOAD * INLINE [

SF7, SF8

S1, -5

S2, -24

];

The pivot table now has SF7 field and sum of F6 is shown if it matches SF8.

Pls look at the attached file.

The "Table 2" pivot table shows the records correctly with more than one F5.

To reduce it to min F5, in "Table 3", I applied your minstring logic like this

IF(Sum(Total <F1, F5> F6)=SF8,

If(MinString(TOTAL <F1, F2, F3, F4, SF7> Aggr(IF(Sum(Total <F1, F5> F6)=SF8, F5), F1, F2, F3, F4, F5)) = F5,

Sum(F6))

, 0

)

I was thinking the aggr will consider SF7/SF8 of that row only but looks like it includes both values.

How do we make the aggr use the SF7/SF8 corresponding to the row?

Re: Pivot table - show first dimension value based on row level condition

sunny_talwar — Mon, 16 Jul 2018 16:57:18 GMT

May be this

IF(Sum(Total <F1, F5> F6)=SF8,

If(MinString(TOTAL <F1, F2, F3, F4, SF7> Aggr(IF(Sum(Total <F1, F5> F6)=SF8, F5), F1, F2, F3, F4, F5, SF7)) = F5,

Sum(F6))

, 0

)