Search by copy and pasting from an external list

By Barry Harmsen

In operationally focused QlikView projects, Search for multiple values by copy-and-pasting from an external list/ 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.

Search example

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

About The Author

Barry Harmsen

Hi there, I'm Barry and I'm a Business Intelligence Consultant at Bitmetric and based in the Netherlands. Originally from a background of 'traditional' Data Warehousing, Business Intelligence and Performance Management, for the past 10 years I have been specializing in Qlik and a more user-centric form of BI. I have done numerous QlikView and Qlik Sense implementations in many different roles and industries. In 2012 I co-authored the book QlikView 11 for Developers. You can follow me on Twitter at @meneerharmsen.


  • 1
    August 22, 2012 - 15:30 | Permalink

    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)

  • 2
    Brian Garland
    August 22, 2012 - 20:00 | Permalink


  • 3
    Matt Nunn
    September 7, 2012 - 07:58 | Permalink

    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.

    • 4
      Ken Walenga
      June 3, 2016 - 18:19 | Permalink

      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’).

      • 5
        Tyler C
        February 9, 2017 - 22:58 | Permalink

        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), ”” & ‘|’), ‘,’, ‘|’) & ‘)’

        • 6
          Matt Nunn
          March 29, 2018 - 00:24 | Permalink

          This is what the expression should be if you are searching for text strings that contains spaces:

          ='(“‘ & replace(‘$(VARIABLE)’, ‘,’, ‘”|”‘) & ‘”)’

  • 7
    October 9, 2012 - 21:59 | Permalink

    Thanks Barry

  • 8
    November 8, 2012 - 16:42 | Permalink

    Fanastic guide. It has saved my hours of trial on error.

    Question: Is it possible to generate an absentee ID list?

  • 9
    November 18, 2012 - 03:55 | Permalink

    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..

    • 10
      June 13, 2014 - 00:52 | Permalink

      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 !!!!


      • 11
        April 5, 2016 - 13:34 | Permalink

        Did you manage to figure out the bookmark method? I am in a similar situation

  • 12
    March 8, 2013 - 23:31 | Permalink

    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?

  • 13
    April 1, 2013 - 14:48 | Permalink

    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:

    Thanks for the blog and your great book!

    • 14
      June 29, 2017 - 10:40 | Permalink

      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

  • 15
    September 27, 2013 - 09:14 | Permalink

    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?

    • 16
      November 7, 2013 - 08:35 | Permalink

      Hi Abhijit,

      While declaring the varaible avoid using “=”
      For variable v1 declare as (str1|*str2*)

  • 17
    October 9, 2014 - 21:41 | Permalink

    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

  • 18
    Brian Garland
    December 6, 2014 - 00:26 | Permalink

    Ralf, your extension works fine for Listboxes but it doesn’t seem to work for the Search object. Is that correct?

  • 19
    May 19, 2015 - 21:16 | Permalink

    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

  • 20
    September 4, 2015 - 07:10 | Permalink

    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

  • 21
    September 5, 2015 - 13:47 | Permalink


    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 !

  • 22
    Barbara Mayrleitner
    July 20, 2016 - 11:13 | Permalink

    Hi Barry,
    Is the download example file no longer available?

    Thank you a lot for your reply!

    Best regards,

  • 23
    October 7, 2016 - 11:43 | Permalink

    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.

  • 24
    Pratik Pahwa
    November 9, 2017 - 08:01 | Permalink

    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?

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Read previous post:
    Show all sheets and sheet objects

    As promised in the previous post, today I have a tip for you that is of real, practical use. Management...