Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
khimhoe_sdm
Creator II
Creator II

Nested If & Pick

Hi all,

I'm trying to build a pivot table where the column and row will be expression. I use the Pick method to create the table. But I have a problem when I have 2 dimensions. When I collapse the first dimension, the row will disappear (due to row value = null). How can I add an expression for the first dimension? It is do-able if I use the Nested If, but the expression will be very messy. I hope there is a way with the Pick Method.

Please refer to the attached QVW for the example between IF vs Pick.

*This is a simplified version of the actual pivot.

*Unchecking the "suppress zero" value is not an option. (I need to suppress null value in DimB)

*This table have multiple expression columns. (not included in this example)

1 Solution

Accepted Solutions
sunny_talwar

May be use this with pick

=Sum(Aggr(Pick(DimA,

Pick(DimB, 1, 2, 3),

Pick(DimB, 4, 5, 6),

Pick(DimB, 7, 8, 9)

), DimA, DimB))

Capture.PNG

View solution in original post

2 Replies
andre_mueller
Partner
Partner

Hi,

if I'm not mistaken, then eclosing the pick functions with an aggregation like Sum() should do the trick.

For your example:

Sum(

Pick(DimA,

Pick(DimB, 1, 2, 3),

Pick(DimB, 4, 5, 6),

Pick(DimB, 7, 8, 9)

)

)

Regards

sunny_talwar

May be use this with pick

=Sum(Aggr(Pick(DimA,

Pick(DimB, 1, 2, 3),

Pick(DimB, 4, 5, 6),

Pick(DimB, 7, 8, 9)

), DimA, DimB))

Capture.PNG

View solution in original post