Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
KHSDM
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