Salesforce innerjoin where in list

[Informatique1]

Hello,

I need to do an innerjoin in a tMap but I don't know exactly how to do it.

If this was an SQL request I should do something like that:

SELECT * FROM Users

WHERE Secteur IN PostalCode

Secteur could be a list like that "55;60;65;70" in the database. And PostalCode is just a string of 2 digits.

Is it possible to add an expression in the tMap here?

Thanks a lot

[Anonymous]

Hello,

In the tMap, you can click the filter button and write a filter expression to filter the output data.

Please have a look at this KB article about:

https://community.talend.com/s/article/tMap-expression-syntax-TCiHW

Hope it helps.

Best regards

Sabrina

[anselmopeixoto]

Hi @BOBE Thomas​ 

Based on the screenshot you provided, it would only be necessary to change the Join Model from Left Outer Join to Inner Join.

That way, compared with a SQL statement, your tMap would be doing something like this:

SELECT * FROM json

INNER JOIN Users ON Users.Secteur = json.PostalCode.substring(0,2)

And since the "Match Model" is configured for "All matches", this join output would be similar to a "IN" behavior in SQL.

[Informatique1 · Accepted solution]

Hello,

I answer late cause of holidays...

Your solution is not exactly what I want. I really need the left outer join because I want to keep all data from my first table (called json here), and then pick up the sectors of the users.

I resolved it by using a tNormalize since my Users table was something like that:

id;role;sectors

123;commercial;31;42;78

tNormalize on sectors, and then inner join + left outer on this with the substring.

Thanks for your time and answer 🙂