Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

infock12
Contributor II

Pick Match syntax issue

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

Tags (1)
1 Solution

Accepted Solutions

Re: Pick Match syntax issue

May be try this

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

6 Replies
MVP
MVP

Re: Pick Match syntax issue

Try

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

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

, ‘Mansell, Tom’

) AS OrderedBy

Re: Pick Match syntax issue

May be try this

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

Highlighted
Partner
Partner

Re: Pick Match syntax issue

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

infock12
Contributor II

Re: Pick Match syntax issue

Thanks Stefan, that worked like a charm too!

infock12
Contributor II

Re: Pick Match syntax issue

Great, thanks Sunny. That works perfectly!

infock12
Contributor II

Re: Pick Match syntax issue

Thanks Jakub, this is very helpful indeed!!