Mapping Two File

[Rohitash]

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.

[Anonymous]

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

[Rohitash]

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.

[Rohitash]

It will always be an "#" character?

[akumar2301]

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

[PhilHibbs]

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.

[PhilHibbs]

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

[Rohitash]

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.

[akumar2301]

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

[PhilHibbs]

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.