Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I need help on an issue regarding text fields and range searching.
Situation:
A table of transactions with corresponding explanation texts.
User wants to search the explanation text for words with a "between" functionality.
E.g. list of text (from QlikView's example file "Executive Dashboard", field: 'Line Desc 1'):
American Beef Bologna
American Cole Slaw
American Corned Beef
Applause Canned Mixed Fruits
Atomic Bubble Gum
Atomic Malted Milk Balls
Cutting Edge Sliced Ham
User wants to be able to somehow type in:
>= American <= Atomic
and get the listed items beginning with 'A' selected. User also wants to type in >=C* <=D* and get these items selected:
American Cole Slaw
American Corned Beef
Applause Canned Mixed Fruits
Cutting Edge Sliced Ham
Are there any native functionalities that apply to these situations? Or do we have to create a field during loading that contains every single word in the "Line Desc 1" field to be able to achieve this?
If so, what would be the best practice to link the parsed text to the data?
Greetings,
Sören
You could load the string in alongside corresponding numeric codes, and then load the strings in again as variables, named using their corresponding numeric codes. You could then use these numeric variable names within set analysis or if statements.
For the user interface you could have two input boxes for users to type in the from and to strings, though I'm not sure how you would then determine which variable matches each of the entries. Alternatively you could have a listbox of the original strings, set with the 'always one value' property, and then use two buttons to say 'use this one' as my from and my to.
Jonathan
Thanks for the hint!
I managed to get a somewhat working solution. There might be a bug though in advanced search featuers in QlikView (using version 11, SR2, 64-bit).
Since QV does not provide any native functionality, and the advanced search feature does not save any typed in searches (i.e. writing an expression containing variables), I was forced to use a macro approach.
1. Make a word list by changing the load script (works quite well if you have to search inside just 1 field):
// Splitting on ' ' (space), making 1 row per word. 5 words on a row = 5 rows in the table
WordList:
load
OrderID, // unique key in FactTable
SubField([Line Desc 1],' ') as SplittedWords
resident FactTable;
2. Show the field as a list box on page (not necessary, but helps the debugging)
3. Create two variables to contain 'from' and 'to' criteria:
- vFrom
- vTo
and show them to the user in an InputBox
4. Create a button that runs a macro called SearchBetween
5. Create the SearchBetween macro:
sub SearchBetween
set f = ActiveDocument.Fields("SplittedWords")
f.Select ""
vFrom = ActiveDocument.Variables("vFrom").GetContent().String
vTo = ActiveDocument.Variables("vTo").GetContent().String
f.Select "= SplittedWords >= '"& vFrom & "' and SplittedWords <= '" & vTo & "'"
end sub
The only problem is the 'to' part of the search. This does not take the '=' - sign in consideration at all.
E.g.
Searching the ExecutiveDashboard with:
vFrom = F*
vTo = H*
the list is only selected with words that starts with F and G. The same phenomena occurs when using advanced search. I.e., probably a bug.
Once again thanks for the hints. It put me on the right track.
Greetings,
Sören