topic Re: Pick/Match Not Working in QlikView

Pick/Match Not Working

Wed, 30 Mar 2016 19:22:18 GMT

Hello Community. Do you see anything wrong with this expression, which is part of a straight table in QlikView 11.2? This builds label header text on an expression.

=pick(alt(match(Only(_DemandDays), 30, 60, 90, 180, 360, 365) + 1, 0),

'Mixed Demand',

'Demand 30 Days',

'Demand 60 Days',

'Demand 90 Days',

'Demand 180 Days',

'Demand 365 Days',

'Demand 365 Days')

When the selections include different demand days, I would expect "Mixed Demand" to appear in the label. Instead, a hyphen appears. However, if I isolate the expression, alt(match(Only(_DemandDays), 30, 60, 90, 180, 360, 365) + 1, 0), it correctly returns 0 under these circumstances. By itself, match(Only(_DemandDays), 30, 60, 90, 180, 360, 365) + 1 returns NULL -- hence the need to wrap the alt() function around it. The other labels are displayed correctly when the Only() function returns a value.

I can't easily provide an example right now. I'm just wondering if there's anything glaringly wrong with the expression. Thank you for looking.

Re: Pick/Match Not Working

sunny_talwar — Wed, 30 Mar 2016 19:26:11 GMT

Not sure why you need Alt?

=Alt(Pick(Match(Only(_DemandDays), 30, 60, 90, 180, 360, 365),

'Demand 30 Days',

'Demand 60 Days',

'Demand 90 Days',

'Demand 180 Days',

'Demand 365 Days',

'Demand 365 Days'),

'Mixed Demand')

Re: Pick/Match Not Working

Wed, 30 Mar 2016 19:29:52 GMT

I explained that. Anyway, your example returns "Mixed Demand" for every selection.

Re: Pick/Match Not Working

sunny_talwar — Wed, 30 Mar 2016 19:34:25 GMT

This seems to be working

=If(Len(Trim(Pick(Match(Only(DemandDays), 30, 60, 90, 180, 360, 365),

'Demand 30 Days',

'Demand 60 Days',

'Demand 90 Days',

'Demand 180 Days',

'Demand 365 Days',

'Demand 365 Days'))) = 0, 'Mixed Demand',

Pick(Match(Only(DemandDays), 30, 60, 90, 180, 360, 365),

'Demand 30 Days',

'Demand 60 Days',

'Demand 90 Days',

'Demand 180 Days',

'Demand 365 Days',

'Demand 365 Days'))

Re: Pick/Match Not Working

swuehl — Wed, 30 Mar 2016 19:36:38 GMT

Or maybe

=pick(alt(match(Only(_DemandDays), 30, 60, 90, 180, 360, 365) , 0)+1,

'Mixed Demand',

'Demand 30 Days',

'Demand 60 Days',

'Demand 90 Days',

'Demand 180 Days',

'Demand 365 Days',

'Demand 365 Days')

Re: Pick/Match Not Working

sunny_talwar — Wed, 30 Mar 2016 19:37:15 GMT

This can be further reduced to this:

=If(Len(Trim(DemandDays)) = 0, 'Mixed Demand',

Pick(Match(Only(DemandDays), 30, 60, 90, 180, 360, 365),

'Demand 30 Days',

'Demand 60 Days',

'Demand 90 Days',

'Demand 180 Days',

'Demand 365 Days',

'Demand 365 Days'))

Re: Pick/Match Not Working

Wed, 30 Mar 2016 19:44:27 GMT

Thank you. That works splendidly.