Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How can we do Word Boundary Match in Qlikview?

I have a following query in SQL server:

SELECT * FROM TABLE_A

DESCRIPTION NOT LIKE '%[^a-z]cos[^a-z]%'


So this query will return values only if it has the text COS as a whole, without any characters [a-z] before or after that.

If I use WILDMATCH ( DESCRIPTION, '*COS*'), it returns rows that have have words like 'ECOSYSTEM' which has characters [a-z] before and after. This is not desirable.


   

DESCRIPTIONSQL SERVERQV WILDMATCH
Abcde COSTRUETRUE
defgh COSTRUETRUE
EcosystemFALSETRUE
TotalcostFALSETRUE
SumofCostFALSETRUE



So my question is what is the correct function or method in qlikview that will return exact same result as the SQL SERVER query I have above.


Thanks,

KS



1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: How can we do Word Boundary Match in Qlikview?

May be This?

= IF(Index(DESCRIPTION, 'COS')>1, 'TRUE', 'FALSE')

View solution in original post

9 Replies
Not applicable

Re: How can we do Word Boundary Match in Qlikview?

WILDMATCH ( DESCRIPTION, '* COS*')

vishsaggi
Esteemed Contributor III

Re: How can we do Word Boundary Match in Qlikview?

May be This?

= IF(Index(DESCRIPTION, 'COS')>1, 'TRUE', 'FALSE')

View solution in original post

Not applicable

Re: How can we do Word Boundary Match in Qlikview?

Hi Rodrigo, WILDMATCH(DESCRIPTION, '*COS*) will not produce desired result for me. It will return rows with words like ECOSYSTEM, which is not correct. I want only rows with text COS as a whole. There should be no other character other than blank spaces

vishsaggi
Esteemed Contributor III

Re: How can we do Word Boundary Match in Qlikview?

Hell Kannan,

Did you get a chance to try my script sent earlier ??

Thanks,
V.

Not applicable

Re: How can we do Word Boundary Match in Qlikview?

Thanks vishaggi. That works only there are no character [a-z] before or after. Unfortunately, the text I have has special characters such as hyphen. like '*-COS*' also. So I am wondering how to discriminate such strings.

Not applicable

Re: How can we do Word Boundary Match in Qlikview?

Hi Vishaagi,

Sorry about the previous comment. Your solution works perfectly now. It works even if there are special characters. Thanks for your suggestion.

Regards,

KS

effinty2112
Honored Contributor

Re: How can we do Word Boundary Match in Qlikview?

Hi Kannan,

I think you've not noticed that Rodrigo's expression using '* COS*' contained a space after the first *.  This would not return ECOSYSTEM.

Cheers

Andrew

Not applicable

Re: How can we do Word Boundary Match in Qlikview?

Thanks Rodrigo. I did not notice the blank space you had inserted  like '* COS*'. So this works.

surender_mehta
New Contributor II

Re: How can we do Word Boundary Match in Qlikview?

Hi ,

 

I am facing similar problem to search the word boundry but somehow not able to achieve. Could you please share the application or the logic you wrote to achieve 

 

thanks in advance