topic Re: Searching range of text data in QlikView

Searching range of text data

Thu, 01 Nov 2012 06:59:03 GMT

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

Re: Searching range of text data

Anonymous — Thu, 01 Nov 2012 07:30:19 GMT

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

Re: Searching range of text data

Thu, 01 Nov 2012 10:47:10 GMT

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