topic Re: Pick match with else condition in QlikView

Pick match with else condition

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

Hello,

I am trying to create a calculated dimension with pick and match with only exception else condition that means if there is no match then pick 'Not in Months' value. I am using the following syntax. However it is showing '-' instead of 'Not in Months' .

It is not picking up the else statement when there is no match. Is there any way to make it to work .

=Pick(Match(Bucket, '01', '12', '23', '34', '45', '56')+1,
'Not in Months','0-1 Month', '1-2 Month ', '2-3 Month', '3-4 Month ', '4-5 Month', '5-6 ')

Re: Pick match with else condition

rajeshqvd — Fri, 21 Jun 2019 12:09:51 GMT

Hi, Use below one

=Pick(1+Match(Bucket, '01', '12', '23', '34', '45', '56'),
'Not in Months','0-1 Month', '1-2 Month ', '2-3 Month', '3-4 Month ', '4-5 Month', '5-6 ')

Re: Pick match with else condition

sunny_talwar — Fri, 21 Jun 2019 12:34:21 GMT

May be try this

=Pick(RangeSum(Match(Bucket, '01', '12', '23', '34', '45', '56'), 1), 'Not in Months','0-1 Month', '1-2 Month ', '2-3 Month', '3-4 Month ', '4-5 Month', '5-6 ')

Re: Pick match with else condition

ashis — Fri, 21 Jun 2019 13:43:57 GMT

Hi Sunny, Awesome it works. Could you please help me to understand how rangesum works here in this case with else statement.

Re: Pick match with else condition

ashis — Fri, 21 Jun 2019 13:45:04 GMT

Hi Rajesh,

Thank you for your reply, i tried that too but did not work out.

Re: Pick match with else condition

sunny_talwar — Fri, 21 Jun 2019 13:56:31 GMT

I think what was happening was that the Match was giving null so Null + 1 = Null... but rangesum handles it differently.... RangeSum(Null, 1) = 1

Re: Pick match with else condition

ashis — Fri, 21 Jun 2019 14:05:18 GMT

Thank you for clarification.