topic Return Like Value in QlikView

Return Like Value

Wed, 26 Aug 2015 20:23:05 GMT

Hi,

I'm a beginner in Qlikview.

Suppose I have 2 tables.

1st table have a field with "JUAN DELA CRUZ" and

2nd table have a field with "JUAN DELA CRUZ JR".

I want that when I select Juan dela cruz on first table, all values containing Juan Dela Cruz on the 2nd table will also show including those with "JR" in it.

How am I supposed to do that?

Thank you

Re: Return Like Value

rwunderlich — Wed, 26 Aug 2015 21:05:54 GMT

QlikView links table only on exact matches. So you will need to normalize all variations to the same value. You can do this using the MAPPING load feature to translate, for example, "JUAN DELA CRUZ JR" to "JUAN DELA CRUZ".

There are also some functions you can use to clean up anticipated variations, such as capitalization. The Capitalize() function can be useful for that (see Help for more on Capitalize).

You may also consider using string functions like PurgeChar() to remove punctuation such as period and commas.

Lastly, you can create your own script function to create a common key for similar sounding names, using algorithms like soundex. I believe there are some examples here on the Forums.

-Rob

Re: Return Like Value

swuehl — Wed, 26 Aug 2015 21:07:27 GMT

Not sure if these tables are linked in your data model and where you want to show the value of your second table.

Maybe you can use a field event trigger on your first field (Settings - Document properties - triggers), then create an action Select - Select in field:

Field:

FieldNameInSecondTable

Search string:

=Only(FieldNameInFirstTable) &'*'

Re: Return Like Value

petter — Wed, 26 Aug 2015 21:15:01 GMT

A simple and straightforward approach could be like this - maybe it suits your specific needs although it might not be a robust general solution.

It is just a Like matching with Name1 appended with the wildcard * to find matches in Name2: