Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
et123
Contributor III
Contributor III

Tmap join on 2 IDs

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

0695b00000PKy0uAAD.png

Labels (2)
1 Solution

Accepted Solutions
Anonymous
Not applicable

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

 

View solution in original post

8 Replies
manodwhb
Champion II
Champion II

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

et123
Contributor III
Contributor III
Author

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
Not applicable

@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
Contributor III
Contributor III
Author

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?

0695b00000PL4tWAAT.png

Anonymous
Not applicable

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
Not applicable

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
Contributor III
Contributor III
Author

thank you! I tried it and it worked perfectly.

Anonymous
Not applicable

Great, thanks for your feedback!