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:
1. An input box: To put the values into a Variable
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:
1. The first part of script cleans the data and applies the selection
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
Select the variable you want to add actions to and click the button under OnInput. This will be either:
“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:
1. 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 the magic that makes it all work is the Search string.
Put this code in the Search String and remember to make sure to replace the Variable name with you variable name.
='(' & Replace(Replace(Replace(Trim('$(LOOKUP_DEBNO)'), ' ', '|'),' ', '|'),
chr(10), '|') & ')'
2. Variable clean up after use
Add another action to set you variable value to nothing
Specify the variable name and give an empty value. This cleans up nicely after each use.
Add only the Set Variable event under the OnChange Section like above.
Take it for a spin
All the setup is now done and we can see that it all works.
The most popular use case where this will be used is user copying items from Excel reports into your field.
But this should work with any space separated list. You can make some changes if you rather want another delimiter like commas.
Load some sample data:
LOAD * INLINE [
DEB001, John Smith
DEB002, Tom Sparks
DEB003, Jerry Fuller
DEB005, Hank Miller
DEB006, Tommy Tippy
DEB007, Jan Smits
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:
There you have it. Hope you enjoyed the post. This is just one way to add some functionality.
How did you attempt to solve this request in the past? Feel free to share your thoughts.