Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
infock12
Creator III
Creator III

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

1 Solution

Accepted Solutions
sunny_talwar

May be try this

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

View solution in original post

6 Replies
swuehl
MVP
MVP

Try

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

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

, ‘Mansell, Tom’

) AS OrderedBy

sunny_talwar

May be try this

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

kuba_michalik
Partner - Specialist
Partner - Specialist

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
Creator III
Creator III
Author

Thanks Stefan, that worked like a charm too!

infock12
Creator III
Creator III
Author

Great, thanks Sunny. That works perfectly!

infock12
Creator III
Creator III
Author

Thanks Jakub, this is very helpful indeed!!