topic Re: Pick Match syntax issue in QlikView

Pick Match syntax issue

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

Hi all, I am trying to do a simple pick match in load script but something is not working. I believe it is a simple syntax issue but can't figure out what. This is what I am using:

Pick(Match(SubField([Order Name], ':',2), ‘Patrick, Fitz’), ‘Mansell, Tom’, SubField([Order Name], ':',2)) AS OrderedBy

It converts the first part, so it changes the name from Fitz Patrick to Tom Mansell and appears on the chart. However, it groups all the other users into a null values. The result I am getting is.

User Total

Tom Mansell 100

- 900

Please help!

Thanks

Karthik

Re: Pick Match syntax issue

swuehl — Mon, 06 Mar 2017 10:10:23 GMT

Try

Pick(1+Match(SubField([Order Name], ':',2), ‘Patrick, Fitz’)

, SubField([Order Name], ':',2)

, ‘Mansell, Tom’

) AS OrderedBy

Re: Pick Match syntax issue

sunny_talwar — Mon, 06 Mar 2017 10:11:43 GMT

May be try this

Pick(RangeSum(Match(SubField([Order Name], ':',2), 'Patrick, Fitz'), 1), SubField([Order Name], ':',2), 'Mansell, Tom') AS OrderedBy

Re: Pick Match syntax issue

kuba_michalik — Mon, 06 Mar 2017 10:32:26 GMT

Pick takes 1, 2, 3... as input, returns null if the input is invalid (<1 or >number of specified options) and Match returns 0 when no match is found (which is invalid for Pick). So they won't work together out of the box if you need a default option. For how to tweak it to achieve such an option, you alredy have 2 good suggestions, but I thought you might appreciate an explanation as well

Re: Pick Match syntax issue

infock12 — Mon, 06 Mar 2017 10:59:22 GMT

Thanks Stefan, that worked like a charm too!

Re: Pick Match syntax issue

infock12 — Mon, 06 Mar 2017 10:59:39 GMT

Great, thanks Sunny. That works perfectly!

Re: Pick Match syntax issue

infock12 — Mon, 06 Mar 2017 11:00:29 GMT

Thanks Jakub, this is very helpful indeed!!