Using contains logic in tMap

[jimbo20814]

Hello, I’m unable to perform a join in tMap using contains logic. I need to be able to do a JOIN using contains logic, not exact match. So if mainflow column string is contained within lookup column string, then perform a JOIN. I tried so many different ways but unable to make it work. Does anyone have experience with this?

[jimbo20814]

To further elaborate..

Okay so basically the main flow has a long URL i.e. “dev.sapience.azure.portal.com/abc/123/bblahblah.aspx?param=bac”. Then in a lookup we have a list of configured URLs but shorter, for example, “azure.portal.com”. See how the lookup value URL is contained within the main flow URL? It’s not an exact match obviously but mainFlow.URL.contains(lookup.URL) == true. So the lookup has some integer ID associated to “azure.portal.com”. I want to perform a JOIN based on contains logic so I can get the lookup ID. Does this make sense?

I tried so many ways to make this work, spending hours fiddling but nothing I tried worked!

[jimbo20814]

A Talend resource told me this would work but it's not working. Actually a string.equals(string) does not work perform a match either. Is this a bug in Talend? How else are you supposed to do contains logic between main flow and a lookup?

[jimbo20814]

Note, when I do a a normal join in tMap (connecting lines) it performs the match. But when I do the .contains or .equals, no match. 

[jimbo20814]

Guys, when I removed the normal join (connecting lines) on CompanyID and did it in the expression, it worked. I guess you can't combine normal JOINS with expressions?

[TRF · Accepted solution]

Here a solution to your case.

1rst of all, the whole job design:

Here, I use tFixedFlowInput for both main and lookup input (as this is just a test job).

The main change from what you've tried is in tMap_1 where the Lookup Model is set to "Reload at each row" instead of "Load once".  This means that for each row from the main flow, the lookup table (here represented by the tFixedFlowInput_2 component) is accessed. As this data source is fix (it is not possible to have a where clause on it), I add a 2nd tMap component to filter on desired records.

Here is what the tMap_1 component looks like:

As you can see, when you set Lookup Model to "Reload at each row" you have to define a global variable for each field from the main flow you want to use to filter on the lookup flow. Here, I define a global variable called "shortURL" which is populated from the corresponding main flow field.

Then, each time a new row arrives from the main flow, the "shortURL" global variable changes and the lookup flow restarts. Each row from the lookup flow comes to the 2nd tMap where it is filtered with the desired logic. Here it is:

The tMap_2 filter is defined with the expected expression allowing to get only records the field called "longURL" contains the value of the "shortURL" global variable.

Finally, I have the following content for the main table:

And this one for the lookup table:

And the result is as expected:

.---------+------------------+----------------------------------------------------------------.
|                                          tLogRow_1                                          |
|=--------+------------------+---------------------------------------------------------------=|
|companyId|shortURL          |longURL                                                         |
|=--------+------------------+---------------------------------------------------------------=|
|C1       |azure.portal.com  |dev.sapience.azure.portal.com/abc/123/bblahblah.aspx?param=bac  |
|C2       |zorglub.portal.com|dev.sapience.zorglub.portal.com/abc/123/bblahblah.aspx?param=bac|
'---------+------------------+----------------------------------------------------------------'

Hope this helps.

[jimbo20814]

Thank you TRF!