In operationally focused QlikView projects, 
I’m often asked is if it’s possible to search for a list of values in a list box by copy-and-pasting from an external source. For example, searching for customer ID’s by pasting values from an Excel spreadsheet.
Unfortunately, out of the box, QlikView does not support this method of searching for multiple values. However, by combining an input box, a variable and a trigger, we can approximate this functionality quite nicely.
Please note that this solution only works well in the full Windows client or plugin, for the AJAX client have a look the the Copy/Paste Search Extension object.
The first thing we need to know is that, when performing a search in a list box, we can explicitly select multiple values by separating them using the pipe symbol (“|”, also known as a vertical bar) and encapsulating the entire list in parentheses. For example, to select the values 8, 21 and 2012 we enter the search string (8|21|2012). As you can see in the image below, this method only selects those values that match the search string. Be aware that there’s no notification of non-matching ID’s.
Once we know how to search for multiple values, all we need to do is convert our copy-and-pasted list into this format and perform the search.
This is done by using an input box, a variable and a trigger. We use the input box to receive the user’s pasted list of values and store them into a variable. Next, we define a trigger on the variable (via Settings | Document Properties | Triggers | Add Action(s)) that converts the list and performs the search by using the following two actions:
- Select in Field: when pasting values from a list, depending on the source, I’ve found that most of the time values are separated by either a return, or by one or two spaces (YMMV). By using a few nested replace statements we can convert this into a pipe-separated list.
='(‘ & Replace(Replace(Replace(Trim(‘$(vCustomerList)’), ‘ ‘, ‘|’),’ ‘, ‘|’), chr(10), ‘|’) & ‘)
- Set Variable: once the selection has been made, we reset the variable to an empty string. This isn’t really necessary, but it makes the input box look tidy.
And that’s how it’s done. Users can now search for multiple values at once by copying and pasting from an external source. You can download the full example application below.
Download the Copy/Paste Search example application
25 Comments
Great article thanks – never knew you could select multiple values in that way.
Can’t seem to paste the IDs into the input box in your qvw ? I choose edit > paste but nothing happens (QV11 SR1)
Excellent!
Fantastic – exactly what I needed. Just be aware that if you are searching text values that contain spaces you will need to remove those spaces in your load script.
You don’t need to modify the load script when your values contain embedded spaces. You just need to use single-quotes to delimit the values, e.g., (‘this is value 1’|’this is value 2’).
How are you able to enter single qoutes in the results of the input box? I recently tried this but it does not work:
='(‘ & ”” & Replace(Replace(Trim(‘$(VARIABLE)’), chr(10), ”” & ‘|’), ‘,’, ‘|’) & ‘)’
This is what the expression should be if you are searching for text strings that contains spaces:
='(“‘ & replace(‘$(VARIABLE)’, ‘,’, ‘”|”‘) & ‘”)’
Thanks Barry
Fanastic guide. It has saved my hours of trial on error.
Question: Is it possible to generate an absentee ID list?
Hi Barry..
Thanks for ur comment.
Wonderful work.. but it has one disadvanatage is ur code will not work for larger data sets.. but book mark method will work.. i have done this..
Hi,
can you describe the “bookmark method”? I am very interested as i have to find a solution on a listbox with 4 mio partnumbers where we want to select always a couple of thousands based on external lists !!!!
Jens
Hi,
Did you manage to figure out the bookmark method? I am in a similar situation
Nice. I’ve been using a variation of this using mixmatch, but I like the simplicity of this better. Could you modify it to also replace Commas and Semi-Colons? Just to cover the bases a bit more?
Hi Barry,
In a document where I implemented this solution, the field in the document contained a material number with lots of leading zeros concatenated to a material description. The list of material numbers in the users spreadsheet didn’t contain the leading zeros and, of course, had no material description on them.
I got it to work by changing the expression slightly so that the string created by the expression looks something like this:
(*material1*|*material2*|*material3*|*material4*)
Thanks for the blog and your great book!
-Tim
Hi Tim,
Please tell me what is the change you have made this to work. I have a requirement where space between the field values is expected. How the expression should be changed to incorporate this
I made change as below
='(‘ & Replace(Replace(Trim(‘$(vCustomerList)’), ‘ ‘, ‘|’), chr(10), ‘|’) & ‘)
I am getting the expected string in the current selection field but filtering based on the string (String with space between) is not happening
Can you please help me with this
Thanks for the post Bary.
For me,
selecting multiple values with Pipe symbol is possible with list box having numeric values.
However I’m not able to seach for multiple values in a list box having alphanumeric values.
Am i doing anything wrong? Or something more needs to be added to syntax to achieve this?
Hi Abhijit,
While declaring the varaible avoid using “=”
For variable v1 declare as (str1|*str2*)
I’ve just released a document extension which enhances copy/paste from external list (eg. Excel) for IE and QlikView Desktop WebView here:
QlikView Extension ListboxSearchPaste
Ralf, your extension works fine for Listboxes but it doesn’t seem to work for the Search object. Is that correct?
I had a situation where the list of values originally came from a few dozen cells from the same spreadsheet row (instead of a column). To make the expression work, I had to add another replace function that replaces the horizontal tab chr(09) with a pipe character.
Thanks, Barry
Excellent post.
Thank you so much.
Can you also help for comma separated?
I changed the expression to :
='(‘ & Replace(Replace(Replace(Trim(‘$(vCustomerList)’), ‘ ‘, ‘,’),’ ‘, ‘,’), chr(10), ‘,’) & ‘)
But it is not working!
Huge Thanks
– Deep
Barry
I was trying to do exactly this using a macro using ActiveDocument.Fields and SelectValues. It worked fine for text values but did not work for numeric values for some reason. Anyway I found your article and yours is a much more efficient, elegant solution.
thank you so much !
Shirley
Hi Barry,
Is the download example file no longer available?
Thank you a lot for your reply!
Best regards,
Barbara
are there any ways to increase the size of Inputbox. When the user wants to paste there inputs they are not able to see the pasted values, as in the web version, the entries goes down copied from an excel.
Hi Barry,
Can you please answer on how can we follow same approach in qliksense. Do we have to use extension for the same? and if yes then which extension should we use?