Join an input to a lookup with a contains condition

Six Stars

Join an input to a lookup with a contains condition

Hi

I'm trying to take an input file containing a free text notes field that I'd like to join to a lookup.  The outcome of the lookup needs to attach the description in the lookup table to my output that is flowing through the tMap.

 

So - input sting could contain "ABCD Dijengedkrt DaveDeffred"

My lookup contains a description of Dave.

 

What I want is for my input file to flow through and join to the  Lookup and for he description of Dave to be attach to the output. 

 

You will see in the attached image that I have 180 records flowing in that joins to 50k records in the lookup.  This is a direct match and so ideally we want to do a contains lookup.

 

Any help would be much appreciated.

 

Thanks

Dave

Highlighted
Employee

Re: Join an input to a lookup with a contains condition

Hi Dave,

 

     Would you like to check all the lookup values for each input text?

 

      For example, if the input text is "ABCD Nikhil PQRS Dave" and we are having values for both Nikhil and Dave in lookup table. In this case, would you like to pick the data for Nikhil and Dave or only one of them?

 

      Another question is that do we need to check the lookup value for any specific area of the input string or it can be from anywhere?

 

      Please also note that it will give lot of results if more than one match is present for each input text. For example, if there are 100 lines in put text and each of them had two matches with lookup values, then your output will be 200 lines. Do you want that way?

 

      If you could give more details about your use case, it would be easy for us to analyse further.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Six Stars

Re: Join an input to a lookup with a contains condition

Hi Nikhil

 

Thanks for helping.  I can just take the one match to the lookup thats fine. 

 

So, if we have input containing :

"ABCD Dave 123"

 

and the lookup contains:

Dave

BC

 

Then I'm happy for my output to be tagged with 

Dave

 

So my output row if I take through the input string as well will be

"ABCD Dave 123","Dave".

 

Hope that helps work through a solution for me.

Thanks

Dave

Employee

Re: Join an input to a lookup with a contains condition

Hi Dave,

 

     I would say, you will have to use a Cross join between input flow and lookup flow as the first step. The link to do it is as shown below.

 

https://community.talend.com/t5/Design-and-Development/How-to-perform-a-CROSS-JOIN-with-Talend/td-p/...

 

    Once the product join output is ready, you will have to use another tMap and in the output expression, you can use the java contain function between input column and lookup column. This will make sure that you will get only matched output.

 

    Now, since you are doing product join, there might be chances of multiple records in the output. So you will have to do a taggregaterow to pick the first record in your group.

 

    At this point, you will get your desired output. If you need the records which do not hav any match, then you will have to store this output in a separate data set.

 

    Then read the original dataset and do inner join with new dataset. All the rejects of this inner join will provide the records with no match.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now