2 Replies Latest reply: Nov 1, 2012 6:47 AM by Sören Hakola RSS

    Searching range of text data

      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
          Jonathan Brough

          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

              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