topic Re: Using a comma-separated list of values in Pick() function in QlikView

Using a comma-separated list of values in Pick() function

Wed, 25 Nov 2020 16:16:04 GMT

Hi there,

I have a LET variable named vList, with the following value:

''Booking Confirmation', 'Container Documentation', 'Document Accuracy', 'Shipper Sent Docs''

As you can see, it's a list of comma separated values where each value is enclosed in single quotes, and finally the whole list is enclosed in single quotes as well.

I want to use this variable in the following expression:

=match(vState, vList)

vState is another variable, its value can be any of the values in the list, and I want to know the position of vState value in the list using the match function. The match() function will be used in an expression in the 'Show Conditional' field in a chart object.

Thing is, I can't get this to work as I expect. I've tried:

=match(vState, vList) the result is 0

=match(vState, $(vList)) the result is -

Could someone please kindly point me in the right direction?

Thanks in advance,

-Eduardo

Re: Using a comma-separated list of values in Pick() function

sunny_talwar — Wed, 08 Jun 2016 19:33:40 GMT

Do you have a sample you can share?

Re: Using a comma-separated list of values in Pick() function

Colin-Albert — Wed, 08 Jun 2016 20:00:36 GMT

=Match( 'C', 'A', 'B', 'C','D') returns 3 as you would expect/

Perhaps you need to enclose your first variable in quotes

Have you tried match('$(vState)', $(vList) ) ?

Re: Using a comma-separated list of values in Pick() function

trdandamudi — Wed, 08 Jun 2016 20:48:52 GMT

Here is the working solution:

Let vList = Chr(39) &'Booking Confirmation' &Chr(39) &',' &Chr(39) &'Container Documentation' &Chr(39)&',' &Chr(39) &'Document Accuracy' &Chr(39)&',' &Chr(39) &'Shipper Sent Docs' &Chr(39);

Let vState= 'Document Accuracy';

In text box:

=Match('$(vState)',$(vList)) <--- This will give you the result 3

Note: Attached is the working qlikview file. Hope this helps...

Re: Using a comma-separated list of values in Pick() function

Wed, 08 Jun 2016 23:14:55 GMT

Thanks Thirumala, it worked. The key was to remove the outmost single quotes, as shown by your sample file.

vList value should be

'Booking Confirmation', 'Container Documentation', 'Document Accuracy', 'Shipper Sent Docs'

NOT

''Booking Confirmation', 'Container Documentation', 'Document Accuracy', 'Shipper Sent Docs''

Best Regards,

-Eduardo

Re: Using a comma-separated list of values in Pick() function

trdandamudi — Thu, 09 Jun 2016 11:20:14 GMT

I am glad that it worked out....