Tmap join on 2 IDs

[et123]

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

[manodwhb]

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

[et123]

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 

[Anonymous]

@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

[et123]

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?

[Anonymous · Accepted solution]

Hi

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

[Anonymous]

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.

[et123]

thank you! I tried it and it worked perfectly.

[Anonymous]

Great, thanks for your feedback!