topic Re: search string with multiple values in QlikView

search string with multiple values

Thu, 04 Sep 2014 16:05:18 GMT

I currently have a field where each value is a large amount of free form text. I am using a variable in Set Analysis to search for certain key words and count how many of these field values contain any of these words. I also want to allow the end user to enter in (via an input box) the terms for which they want to search. They could possibly enter only one term or they could enter twenty. How do you assign those field values to the variable such that the Set Analysis can do an 'OR' search on them (regardless of how they are delimited)? For example, a User enters in 'cigarette match ashes' in the search field. How do I create the variable "vTermMatch' such that when the Set Analysis, which is set up as follows:

COUNT({$<[FreeFormText] = {"*$(=vTermMatch)*"}>} [CountTerm])

ends up being equivalent to :

COUNT( {$<[FreeFormText] = {'*cigarette*', '*match*', '*ashes*'}>} [CountTerm]) ?

Re: search string with multiple values

Gysbert_Wassenaar — Fri, 05 Sep 2014 07:41:13 GMT

Perhaps with another helper variable:

vSM = '{$<[FreeFormText] = {' & chr(39) & '*' & replace( '$(vTermMatch)',' ', '*' & chr(39) & ',' & chr(39) & '*' ) & '*' & Chr(39) & '}>}'

COUNT( $(vSM) [CountTerm])

Re: search string with multiple values

Mon, 22 Sep 2014 16:30:17 GMT

Thank you Gysbert. This was helpful. Do you have any thoughts as to how this could be done if the user were to enter in a search string where THEYget to determine if it is an "AND" search or an "OR" search? For example, the user wants to search for "Cigarettes AND ashes" as opposed to "Cigarettes OR ashes". How could you get that entered into Set Analysis. FYI, I am trying to use Set Analysis so that my charts and tables will update by the search term(s) as well.

Re: search string with multiple values

Gysbert_Wassenaar — Tue, 23 Sep 2014 09:54:26 GMT

You could try to tinker together something in Qlikview using if-statements and the substringcount function. But that can get ugly quickly. Perhaps you should use something like lucene+solr (lucene.apache.org/solr/) to index and search your texts if you want to do this kind of text analysis.