Who also hates data hunting? Trying to find information on a list of items, but hating to CTRL select them in long endless lists of selections. Wouldn’t it be nice if you can just copy and paste the list of items into a box and have Qlikview select them? Read on then.
Here is a solution that enables you to do just that with fields of data like company codes, invoice numbers, employee numbers, etc. This code sample I will be giving works great, but currently only for fields without spaces in the data. As the lists we paste in are space delimited. But I am sure with some effort we can make some upgrades in a next article.
The solution uses 2 parts to make it work:
2. Some Variable Triggers: To clean up and apply the selection
I am going to use Debtor Number as an example field. Here are the implementation steps
Create a new variable and input box on the screen
Tip: I prefix the variable names with LOOKUP_ to sort them neatly
Create a new input box on the screen and create a new variable by clicking on “New Variable”.
Here I called the variable “LOOKUP_DEBNO” with a label “DEBTOR NO” which reads better for the users.
Setup the Variable triggers and scripting
Now we need to make something happen when we paste data into the field on the screen.
We will be configuring 2 parts here:
2. The last part cleans the variable to prepare for the next selection when needed.
Finding the triggers
To Find the Triggers go to: Settings > Document Properties and select the Triggers tab.
Look at the bottom for the Variable Event Triggers section.
Setup the OnIput and OnChange actions
“Add/Edit Actions” depending if there are already actions.
Now we want to add 2 Actions in the OnInput and 1 Action in the OnChange section:
Here’s how to select in field:
- Click on the Add Button to add a new action.
- Then pick Selection > Select in Field action
Now populate the fields needed to drive the trigger. Enter the dimension/data field for the selection.
In my example “DEBTORNO”.
Now you add the magic that makes it all work is the Search string.
chr(10), ‘|’) & ‘)’
Add another action to set you variable value to nothing.
Take it for a spin
All the setup is now done and we can see that it all works.
Load some sample data:
For an Excel list copy a couple of item you want to select and paste it into the input box:
After you press enter you will see the selections applied: