topic Re: How to get rows matched and rows not matched to the same table? in Talend Studio

How to get rows matched and rows not matched to the same table?

EvoticTalend — Sat, 16 Nov 2024 00:01:52 GMT

Hello everyone!

I have two excel files with this columns (example):

EXCEL 1

ID -- Name - Age

1--Paul--31

2--Ramon--26

3--Chris--29

EXCEL 2

ID --Countries visited -- Number of brothers

1--5--2

3--2--1

4--6--4

I WANT a TABLE LIKE THIS:

TABLE

ID(PK) --Name-Age-Countries visited--Number of brothers

1--Paul--31--5--2

2--Ramon--26--NULL--NULL

3--Chris--29--2--1

4--NULL--NULL--6--4

WHATS THE PROBLEM? I'm doing a JOIN in a t_Map between EXCEL1.ID and EXCEL2.ID, but the ID's that not matched are NULLS (like ID 2 and 4), and I can't have a Primary Key as NULL.

I want the rows that matched and the rows that not matched.

How can I reach that?

Re: How to get rows matched and rows not matched to the same table?

manodwhb — Mon, 21 Jun 2021 10:26:00 GMT

@Ivan R ,you do with tmap

Flow 1) :- EXCEL 1 is primary and EXCEL 2 is lookup and do leftouter join and get all the columns

Flow 2) :-EXCEL 2 is primary and EXCEL 1 is lookup and do the inner join and get the inner join rejects.

And both outputs connected to tUnion component and you will get required output.

Thanks,

Manohar

Re: How to get rows matched and rows not matched to the same table?

EvoticTalend — Mon, 21 Jun 2021 10:29:27 GMT

Hi Manohar! thanks for the response, appreciate it.

Im trying your way but I dont find the tUnion component, maybe it is tUnite?

Re: How to get rows matched and rows not matched to the same table?

manodwhb — Mon, 21 Jun 2021 10:45:14 GMT

Sorry it is my bad tUnite.