topic Re: User Input List - Multiple Text Values as Selection in QlikView

User Input List - Multiple Text Values as Selection

Anonymous — Tue, 29 Jul 2014 12:49:25 GMT

Hello,

I know this question has been posted here before but my data is somewhat different than most. I am an engineer, not a developer. I have large data sets of technical (not sales) data dealing with information about vehicles (identified by their unique 17 digit alphanumeric VIN). VIN = Vehicle Identification Number, e.g. 1A2B3C4DE5F987654. Each VIN is unique but each VIN can have thousands of records in the data. I need a way for the user to input a list of VINs and pass those into the selection for the 'VIN' field. This is only one of several ways that VINs can be selected so I need to be able to do it without reload (preferably copy and paste out of an Excel sheet). From what I have read on other posts, this is not an easy task.

What I've tried so far (unsuccessfully):

Pasting list from excel into a single line of a list box > using a button with concat() function to pass the list box variable (which a tab delimited list of VINs) into the selection for VIN
From Is there a way to have an input field allow for multiple lines of text?
1. List box to text box
  - User must enter values line by line
  - I don't believe this will allow me to pass the values into selection.
  - Requires a variable for each line of input > not practical because I need to allow at least 75-100 VIN inputs

Again, I am engineer doing a developer's job so I am not proficient with macros/VBA. Any help here would be GREATLY appreciated.

Thanks,

Re: User Input List - Multiple Text Values as Selection

steinrich — Fri, 06 Mar 2015 14:40:02 GMT

Hi Casey,

I appreciate if you have a solution working and would really like to now if QV is feasible to handle multiple input values for selection.

Please let me know.

Richard

Re: User Input List - Multiple Text Values as Selection

martynlloyd — Fri, 10 Jun 2016 12:04:25 GMT

Hi Casey,

Did you solve your user input problem? I have a very similar requirement to be able to paste in a list of chassis numbers to be selected.

Kind regards,

Re: User Input List - Multiple Text Values as Selection

Anonymous — Fri, 10 Jun 2016 12:31:45 GMT

Yes I did actually solve this after many months of trial and error. So here is how it works:

Create a variable vVIN_List and an input box for it. User copies and pastes their VIN list from Excel into this input box so the varible vVIN_List becomes something like this (these are all made up VINs): 1A23DEFG32H123456 1B45DEFG32H789456 1C67DEFG32H654987 2A89DEFG32H123456 2B10DEFG32H987321 2C11DEFG32H789123 3A12DEFG32H147963 3B13DEFG32H258741 3C14DEFG32H369741
Set up a variable event trigger that converts this into a search mask and passes it into the selection in the VIN field. So the variable event trigger for vVIN_List looks like this:
     Trigger: OnInput
     Action: Select in Field
     Field: VIN
     Search String: ='(' & Replace(Replace(Replace(Trim(vVIN_List), ' ', '|'),' ', '|'), chr(10), '|') & ')'
This converts vVIN_List to this and passes this search mask into the VIN field:
(1A23DEFG32H123456|1B45DEFG32H789456|1C67DEFG32H654987|2A89DEFG32H123456|2B10DEFG32H987321|2C11DEFG32H789123|3A12DEFG32H147963|3B13DEFG32H258741|3C14DEFG32H369741)

NOTE: This has to all be done in one step because the variable event trigger actions are not done sequentially. This was an issue for me in my previous attempts.

I also created a text box which pops up when a user inputs a VIN list that displays the number of VINs they pasted and the number of VINs that were actually found/selected. This is the text box expression:

=GetSelectedCount(VIN)&' of '&vNoPastedVins &' pasted VINs found (Click to dismiss)'

where the variable vNoPastedVins =len(purgechar(vVIN_List,' '))/17

So far I haven't found a limit to the number of VINs that can be pasted either! It can handle tens of thousands at a time. Hope this helps.

Re: User Input List - Multiple Text Values as Selection

Anonymous — Fri, 10 Jun 2016 12:32:45 GMT

See solution below.

Re: User Input List - Multiple Text Values as Selection

martynlloyd — Fri, 10 Jun 2016 16:15:13 GMT

That's fantastic,

many thanks Casey!

Marty.

Re: User Input List - Multiple Text Values as Selection

steinrich — Thu, 16 Jun 2016 09:38:21 GMT

Great job. Thanks to find this out and share to the community.

Re: User Input List - Multiple Text Values as Selection

shane_spencer — Mon, 02 Oct 2017 10:43:00 GMT

Casey that is so awesome - saved me a load of time/coding! Thanx!

Re: User Input List - Multiple Text Values as Selection

userid128223 — Mon, 26 Aug 2019 20:21:39 GMT

Your solution works perfectly except for when input has spaces. How do you handle that. In my case the input with spaces did not get selected and gets ignored.

for example.

If you have:

temp

temp1

temp3

temp 12

in above case temp1, temp3 gets selected and "temp 12" get ignored. How do you make changes to your script so it can handle spaces in input box.

Re: User Input List - Multiple Text Values as Selection

SrinithiS — Fri, 28 Nov 2025 13:00:49 GMT

@userid128223 Have you got any solution to this. I am also sailing on same both