topic Re: Adding * around every word in QlikView

Adding * around every word

padmanabhv — Sat, 16 Nov 2024 19:33:28 GMT

Hi,
I am trying to get an ACTION to perform a few string functions.

I figured out replacing the AND/OR but am having trouble trying to add '*' at the start and end of each word.
For example, if vInPut = (apple AND orange) OR mango

I want output = (*apple*,*orange*) | *mango*

REPLACE helps with changing the AND to , and OR to |
But how do I get a '*' around each word.

(PS. I need this specific format to perform a search, hence the *)
I could have the user enter , but I have picky users who would call this an "inconvenience"

PPS. Keep in mind there could be multiple such AND and OR combinations, so cannot assume there will be a fixed format, meaning you cannot REPLACE (vinput, '(','(*') because the ( may not always exist.

TLDR: ACTION string function to convert 'apple' to '*apple*' , and every 'word' converts to '*word*'

Thank you !

Re: Adding * around every word

sunny_talwar — Mon, 02 Dec 2019 15:37:22 GMT

May be like this?

=Concat( If(Len(SubField(Replace(Replace(Replace(Replace('(apple AND orange) OR mango', 'AND', ','), 'OR', '|'), ')', ' )'), '(', '( '), ' ', ValueLoop(1, 10))) > 1, Chr(39) & '*', '') & SubField(Replace(Replace(Replace(Replace('(apple AND orange) OR mango', 'AND', ','), 'OR', '|'), ')', ' )'), '(', '( '), ' ', ValueLoop(1, 10)) & If(Len(SubField(Replace(Replace(Replace(Replace('(apple AND orange) OR mango', 'AND', ','), 'OR', '|'), ')', ' )'), '(', '( '), ' ', ValueLoop(1, 10))) > 1, '*' & Chr(39), '') , '', ValueLoop(1, 10))

Re: Adding * around every word

marcus_sommer — Mon, 02 Dec 2019 16:15:28 GMT

Just adding the wildcard around the words won't be enough. I think you need to take the efforts to build a rather larger logic with multiple if-loops in which you:

count the number of words
count the number of AND and OR and XOR
count the number of brackets () and are all closed + are they nested
remove invalid chars (maybe tabs, various kind of quotes, …)
clean multiple spaces
replace spaces and/or terms like " and " with "* & *" (there might be various types)
consider to add also double-quotes - search-strings which contain a space or other special chars needs to be wrapped with quotes
…

It's hard work to develop a routine which fetched all possibilities … and if the users make logically mistakes with the AND's and OR's and the appropriate nesting with brackets the returned results won't be like they are expected. Therefore the more rules you could set the easier would be your task and the better would be the results.

Here a posting which provides nearly all information which are available to a compound search: Compound-Search-demystified.

- Marcus

Re: Adding * around every word

Brett_Bleess — Tue, 10 Dec 2019 21:09:08 GMT

Did Sunny or Marcus' post help with your use case? If so, do not forget to return to the thread and use the Accept as Solution button on the post(s) that helped, this gives the poster credit for the assistance and lets the other Community Members know it worked for you. If you still require further assistance, leave an update.

Regards,
Brett

Re: Adding * around every word

padmanabhv — Fri, 20 Dec 2019 20:37:16 GMT

Thank you @sunny_talwar ! This seemed to do the trick !
I know its hacky and ideally I should be creating a better solution like @marcus_sommer mentions, but given my time constraints, this hacky fix is perfect !