One Star

Merge two files

Hello,
I guess that my problem has been solved many times. However, I've struggled for two days with Components help, google search, tMap, tJoin, tUnite and I can't find my way out.
Basically, I want to merge file A
PName|Count A
A|7
B|24
D|10
E|11
with file B
PName|Count B
A|9
C|13
D|1
F|8
to get file C such as
PName|Count A|Count B
A|7|9
B|24|0
C|0|13
D|10|1
E|11|0
F|0|8
Thanks a lot for your help.
Cheers
2 REPLIES
One Star

Re: Merge two files

I got this working with 3 Subjobs connected by OnComponentOk:
tFileInputDelimited_1 --> tMap_1 --> tFileOutputDelimited_1
OnComponentOk
tFileInputDelimited_2 --> tMap_2 --> tFileOutputDelimited_2
OnComponentOk
tFileInputDelimited_3 --> tSortRow_1 --> tDenormalizeSortedRow_1 --> tMap_3 --> tLogRow_1

The first two subjobs output both files into one file:
The output schema for the tMaps are both: PName,countA,countB
In tMap1 map pname and countA, not countB of course.
In tMap2 map pname and countB, not countA of course.
The output from tMap1 lands in the file and truncates any content.
The output from tMap2 appends. (Set "Append" for tFileOutputDelimited_2!)
Both files are now "merged" into one file, just not how you want.
This is a temporary file.
The third subjob reads the merged temp-file, sorts the rows and runs them past tDenormalizeSortedRow.
tDenormalizeSortedRow seems to want strings for columnA and columnB
so set the count columns for this section to string and not as integer in the file input schema of tFileInputDelimited_3.
Set both columnA and columnB to denormalize.
Important: use no delimiter in tDenormalizeSortedRow. Set the delimiter to "".
(I assume your input rows have unique keys!)
tMap3 sets empty fields to 0 as in your requirement:
Something along the lines of-
(StringHandling.LEN(row5.countA) > 0) ? row5.countA : "0"
Cast countA and countB to integer if you need to.
Seventeen Stars

Re: Merge two files

hi,
you have to denormalize your data.
1- fill empty column with 0 as value. (you've done the job)
2- read & unite the data
3- denormalize the data .
here 's screenshot for 2&3.


hope it help
regards
laurent