Checking if a field contains a value from a lookup
Hi,
I am trying to achieve the following. Can anyone tell me if this is actually possible and if so, how to do it.
I have an input column that may or may not contain a name. The name would only be a part of the data in the field. There is other information.
I also have a table that contains 100,000+ names.
I want to take the input column, then lookup against the names dataset to check if a name is contained within the input field. I know if there was only the name in the input column, I could easily just use a tjoin. Because there is other data, not sure on what components to use.
Any ideas on how to do this?
Thanks,
Simon
Hi Simon
You can use a tjoin or tMap do a inner join, if the output has rows, that means the input column contains a value from a lookup.
Best regards
Shong
Hi Shong,
I guess what I didn't explain properly is the input field has text (multiple words) in it which only one of may be a name so a lookup using a tJoin would always fail.
What I need would be a lookup similar to in sql the like comparison operator except against multiple lookup values.
e.g. inputrow.field like '%lookuprow.field%'
So is there a component like tJoin or tFuzzyJoin that rather than containing exact match or metaphone for example, has a "contains" operator.
Thanks,
Simon
Hi
I get you now, do a left outer join on tMap, add a filter condition on output table, for example:
row1.input_column.contains(row2.name)
If the output table has rows, that means it contains value from a lookup.
Best regards
Shong
