topic Re: Tmap join on 2 IDs in Talend Studio

Tmap join on 2 IDs

et123 — Thu, 17 Mar 2022 01:35:27 GMT

Hi,

So I have my input 1 has tva_cc and siret_cc and my input 2 has e_VAT and e_SIRET ,

I want to make tmap join on this 2 columns to get e_establishment in my output, but the problem is that in some cases one of them is null.

I tried to fill the output field with :

row2.tva_cc.equals(row4.e_VAT)||row2.siret_cc.equals(row4.e_SIRET)?row4.e_establishment_id:null

but I got nullable exception.

Anyone has an idea how to make a condition where to see if tva_cc == e_VAT so it takes e_establishment to the output and if tva and e_vat are null so I check if the siret_cc == e_siret ?

Thank you

Re: Tmap join on 2 IDs

manodwhb — Thu, 17 Mar 2022 02:02:16 GMT

@eya tounsy ,first you handle nulls and then do the join.

Re: Tmap join on 2 IDs

et123 — Thu, 17 Mar 2022 02:29:56 GMT

I tried this one but didn't work :

(row2.tva_cc)==null||(row2.tva_cc).equals("")&&(row2.siret_cc)==null||(row2.siret_cc).equals("")?null:

(row4.e_VAT)==null||(row4.e_VAT).equals("")&&row4.e_SIRET==null||(row4.e_SIRET).equals("")?null:

(row2.siret_cc==null||(row2.siret_cc).equals(""))&&!(row2.tva_cc==null||(row2.tva_cc).equals(""))&&!(row4.e_VAT==null||(row4.e_VAT).equals(""))&&((row2.tva_cc).equals(row4.e_VAT))?row4.e_establishment_id:

(row2.tva_cc==null||(row2.tva_cc).equals(""))&&!(row2.siret_cc==null||(row2.siret_cc).equals(""))&&!(row4.e_SIRET==null||(row4.e_SIRET).equals(""))&&((row2.siret_cc).equals(row4.e_SIRET))?row4.e_establishment_id:null

Re: Tmap join on 2 IDs

Anonymous — Thu, 17 Mar 2022 05:21:35 GMT

@eya tounsy , if you do left outer join on tMap using two conditions, it equals to row2.tva_cc.equals(row4.e_VAT)&&row2.siret_cc.equals(row4.e_SIRET), it is an 'and' operation.

I think you need to filter the null value from input1 and input2 using tFilterRow first, and then do different joins.

Regards

Shong

Re: Tmap join on 2 IDs

et123 — Thu, 17 Mar 2022 16:31:01 GMT

Hi, Thank you for your time, but I'm just new to talend so tFilterRow will alows me to reject null values? (Screenshot bellow)

As you can see, those rows can be null , so I want to make a condition if there for example one of the tva or vat is null so he can check the siret_cc and e_siret , and if there is no match he can insert the row but without inserting the e_establishment_id in the output, so the tFilterRow will allows this?

Re: Tmap join on 2 IDs

Anonymous — Mon, 21 Mar 2022 02:41:42 GMT

It's impossible to set the condition on tMap to determine which columns are used as join Key, you have to handle the null value before tMap, otherwise, the NPE is thrown. My idea is to use TfilterRow to filter the data, split the source data to two parts, one part contains the data with null in tva column, another part contains the data with non null in tva column. Then, do different join on tMap for each part.

Regards

Shong

Re: Tmap join on 2 IDs

Anonymous — Mon, 21 Mar 2022 12:30:24 GMT

The best thing is to have a replacement for null. For numbers you could use 0 (in case 0 is not a valid value) or use a huge negative number like Long.MAX.

You have to replace the null value just befor the rows reach the tMap.

In SQL you could use the coalesce function.

In the tMap output you can replace the null replacement back into null.

Re: Tmap join on 2 IDs

et123 — Mon, 28 Mar 2022 17:31:08 GMT

thank you! I tried it and it worked perfectly.

Re: Tmap join on 2 IDs

Anonymous — Mon, 28 Mar 2022 23:53:37 GMT

Great, thanks for your feedback!