Pick Match syntax issue

[infock12]

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

[swuehl]

Try

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

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

, ‘Mansell, Tom’

) AS OrderedBy

[sunny_talwar · Accepted solution]

May be try this

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

[kuba_michalik]

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]

Thanks Stefan, that worked like a charm too!

[infock12]

Great, thanks Sunny. That works perfectly!

[infock12]

Thanks Jakub, this is very helpful indeed!!