topic Re: Mapping Two File in Talend Studio

Mapping Two File

Rohitash — Sat, 16 Nov 2024 02:15:45 GMT

Hi Team,

There is a new requirement that came up and it's quite tricky.

Consider , I have two files File A and File B.

File A File B Output

--------- ------------- -------------

Col A Col A Col A

A A A

B B B

C # C

DD ## DD

which means Col A value "C" should be matched with "#" and output should be "C"

similarly , means Col A value "DD" should be matched with "##" and output should be "DD".

How does matching will perform on this situation?

Kindly assist.

Re: Mapping Two File

Anonymous — Tue, 02 Jun 2020 16:02:26 GMT

# means it can be any a character or it is always character "#"?

Re: Mapping Two File

Rohitash — Tue, 02 Jun 2020 16:37:37 GMT

it will always be "#" character.

But for each and every row "#" character, will get vary.

Please find sample below

File A File B Output

------------ ------------ --------------

Col A Col A Col A

===== ======== ========

A A A

B B B

C # C

DD ## DD

EEE ### EEE

kindly assist.

Re: Mapping Two File

Rohitash — Thu, 04 Jun 2020 12:49:45 GMT

It will always be an "#" character?

Re: Mapping Two File

akumar2301 — Thu, 04 Jun 2020 13:56:19 GMT

one way I see is to

1) join two files in column wise (https://community.talend.com/t5/Design-and-Development/Merge-two-files/td-p/98099)

so you out of this step would be

FileCol1,FileCol2

A,A

B,B

DD,##

C,#

2) now use tMap with expression :

FileCol1.equals(FileCol2) ? FileCol1 : (FileCol1.matches(FileCol2.replaceAll("#","."))) ? FileCol1 : ""

FileCol1.matches(FileCol2.replaceAll("#",".")) will ideally do a regex match with number of character in FileCol1 and FileCol2. you can implement it in many other ways. there could be syntax error in above expression.

Abhishek

Re: Mapping Two File

PhilHibbs — Thu, 04 Jun 2020 14:11:39 GMT

I don't understand. How can "C" match "#"? It's "C" so it can't match anything other than "C". And your output is always just the value of File A Column A, so File B seems to be irrelevant anyway.

Re: Mapping Two File

PhilHibbs — Thu, 04 Jun 2020 14:12:48 GMT

You need to use .equals() to compare strings, @uganesh

Re: Mapping Two File

Rohitash — Thu, 04 Jun 2020 16:25:32 GMT

Merging is not possible. Because Schema is different.

Example : Two Files A and B has different schema. I need to fetch the result set from File B.

Please find the scenario below

FILE_A
COL_A	COL_B
AA	12
BB	34
CC	78
DD	56

joining files on the basis of COL_A and COL_B

FILE_B
COL_A	COL_B	COL_C
AA	12	7
BB	34	8
CC	##	9
##	56	10

Results

OUTPUT
COL_A	COL_B	COL_C
AA	12	7
BB	34	8
CC	78	9
DD	56	10

Considering "##" can be any value with file B. but it should match "##" with "DD and value should be "10".

Can you please assist.

Re: Mapping Two File

akumar2301 — Thu, 04 Jun 2020 16:41:57 GMT

To append in same file

1) add rownum for both file
2) to innerjoin coming from each file on rownum (with tmap ) and get all coluns from both file at output

When you have all in same file , build your logic accordingly

Re: Mapping Two File

PhilHibbs — Thu, 04 Jun 2020 16:50:38 GMT

The way I would do this is with three separate lookups.

First lookup on COL_A and COL_B and fill in the COL_C if both match.

Second lookup on COL_A with the reference link just containing values where FILE_B.COL_B is ##, and if there is a match then fill in the COL_C value - up to you whether you override any COL_C value from the first lookup or not.

Third lookup on COL_B with the reference link containing values where FILE_B.COL_A is ##, and if there is a match then fill in the COL_C value.

Re: Mapping Two File

Rohitash — Thu, 04 Jun 2020 17:02:26 GMT

Inner join is not possible, if I add rownum.

Because row count is different.

File a have 50 records , whereas File B has 10 records. As a reference I'll be using only COL_C column from File B.

Any alternative suggestion, will be appreciable.

Thanks You so much for assisting me.

Re: Mapping Two File

PhilHibbs — Fri, 05 Jun 2020 10:05:48 GMT

If FILE_B can ever contain ## in both COL_A and COL_B then you would need a fourth lookup that does a cross join, which I think you would do by having a fixed key value rather than a variable column. Actually it might be best to do the second and third lookups that I suggested by having a dummy column containing '##' for the looked-up COL_B and COL_A values respectively.