topic Re: searching for multiple values in a field in QlikView

searching for multiple values in a field

Thu, 05 Jul 2012 11:33:27 GMT

Hi,

Please see attached example qvw file. If I click Test button with only one search word (for example Test), then the value is searched in the field MES correctly. However, when I want to search 2 values (for example, Test, Prod), then the search fails completely. In the attached example, search value is stored in a variable. The test button uses the action of Select in field (MES) with the search string as ='*'&'$(vTest)'&'*'

Thanks for your help in resolving this multiple search issue

Thanks,

Deepak

Re: searching for multiple values in a field

swuehl — Thu, 05 Jul 2012 11:44:29 GMT

Try using something like

='(*'& Replace('$(vTest)',',','*|*')&'*)'

as search expression in your button action.

Re: searching for multiple values in a field

Thu, 05 Jul 2012 17:08:45 GMT

I tried your solution but doesn't give what I was expecting. Please see attached file updated with your suggested expression.

When I search for Test, Prod I am expecting that all rows in the MES field to be selected now since 'Prod' is also in the last row (which is not selected).

Re: searching for multiple values in a field

MayilVahanan — Thu, 05 Jul 2012 17:15:31 GMT

Hi,

See the attached file ..Don't give the space between test,prod ..

Hope it helps.

Re: searching for multiple values in a field

swuehl — Thu, 05 Jul 2012 17:19:35 GMT

Right,

you can can remove one space after the comma automatically by using

='(*'& Replace(Replace('$(vTest)',', ',','),',','*|*')&'*)'

as search expression. Or maybe there is a better approach, but I can't look into that anymore today.

Re: searching for multiple values in a field

Thu, 05 Jul 2012 20:28:55 GMT

Mayil,Thanks for your suggestion to your remove the space and to swuehl to fix the space after the comma separator. I see the selection work perfectly now for multiple values as in your attachment Test.qvw. However, if the search word itself has space, the button action doesn't select the rows in MES field. Do you know how to handle if the space is in the search string itself like 'This is Test'? Further, is there a way to count the number of selected rows based on the search and display in a table? I am attaching the test file with the search string with a space when the button action doesn't work as expected.

Thanks both for your help.

Re: searching for multiple values in a field

swuehl — Thu, 05 Jul 2012 21:58:40 GMT

Right, the spaces seem to break the search string.

We can use another one, this will look kind of ugly (just copy it to a text box to see the search string uncovered):

Try this as search string in your button action:

='=wildmatch(MES,'&chr(39)&'*'&Replace('$(vTest)',',','*'&chr(39)&','&chr(39)&'*')&'*'&chr(39)&')'

Re: searching for multiple values in a field

Fri, 06 Jul 2012 15:12:09 GMT

Wow...swuehl. Thanks very much for your help. The code works perfectly. I will use this code for other fields as well to do similar search.

Best,

Deepak