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 an independent Business Intelligence Consultant based in the Netherlands. Originally from a background of 'traditional' Data Warehousing, Business Intelligence and Performance Management, for the past few years I have been specializing in QlikView and a more user-centric form of BI. I have done numerous QlikView 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.

12 Comments

  • 1
    Paul
    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

    Excellent!

  • 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
    Rob
    October 9, 2012 - 21:59 | Permalink

    Thanks Barry

  • 5
    Rich
    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?

  • 6
    Hari
    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..

    • 7
      Jens
      June 13, 2014 - 00:52 | Permalink

      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

  • 8
    Nate
    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?

  • 9
    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:
    (*material1*|*material2*|*material3*|*material4*)

    Thanks for the blog and your great book!
    -Tim

  • 10
    Abhijit
    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?

    • 11
      Prabhu
      November 7, 2013 - 08:35 | Permalink

      Hi Abhijit,

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

  • Leave a Reply

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

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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

    Close