topic Re: Inner Join with conditions in Talend Studio

Inner Join with conditions

Mayot — Sat, 16 Nov 2024 01:53:26 GMT

Hello,

In a tMap, i want to do an inner join with conditions.

I read an email subject in main. I want to test this subject with a config file (Excel).

2 kinds of treatments : strict correspondance between subject in mail and subject in config file or regex correspondance.

I don't want to generate over log when mail subject doesn't correspond to a configuration.

I can't see how to do that.

Here's my test :

Mail ---main---> -> ok

tMap

Config -lookup-> -> ko

Filter in ok output

context.activateMatchesOnSubject ? (Mail.Subject).matches(Config.Subject) : (Mail.Subject).equals(Config.Subject)

Filter in ko output

context.activateMatchesOnSubject ? !(Mail.Subject).matches(Config.Subject) : !(Mail.Subject).equals(Config.Subject)

My problem is i generate too much "ko" lines.

Re: Inner Join with conditions

Anonymous — Thu, 16 Jul 2020 04:34:40 GMT

Hi,

you are trying to do inner join on tMap, if you don't set the join keys, each output table will contains main_table.num*lookup_table.num rows. Take a look at these KB articles, it might be helpful for you.

https://community.talend.com/s/article/Doing-an-inner-join-using-a-tMap-component-i5Mrr

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

Re: Inner Join with conditions

Mayot — Thu, 16 Jul 2020 06:52:44 GMT

My problem is I want to perform an inner join with 2 differents conditions :

1. If my boolean matches is true so I perform an inner join with key1.macthes(key2)

2. If my boolean matches is false so I perfom a classic inner join with key1 == key2

I don't know how to perfom the matches join.

Re: Inner Join with conditions

Anonymous — Thu, 16 Jul 2020 08:49:47 GMT

You write filter expression to filter the output rows, context.activateMatchesOnSubject is a context variable, it loads a default value or it is assigned value by other subjob before doing the join? Have a try to change your expression to:

for 1.

context.activateMatchesOnSubject ==true&&(Mail.Subject).matches(Config.Subject)

for 2.

context.activateMatchesOnSubject ==false&&Mail.Subject.equals(Config.Subject)

Re: Inner Join with conditions

Mayot — Mon, 20 Jul 2020 07:02:50 GMT

Thanks its the way I do this.

Since my main flow is in iterate (so flow of 1 line by 1 line), I can do a cross join, this doesn't multiply the lines in output. Then I filter the output row with the ternary operation rule.

I handle the rejet with a if after the tMap, if there's no line in output, it mean the join don't work.