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

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