Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

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

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.

1 Solution

Accepted Solutions

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

View solution in original post

12 Replies

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

Contributor III
Contributor III

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

Try this

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


Capture.PNG

Contributor III
Contributor III

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

];

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

View solution in original post

Contributor III
Contributor III

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?

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

Contributor III
Contributor III

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.

Contributor III
Contributor III

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