You will need to create Source and Target objects for your flat files. Then, in a mapping, you can use Joiner transformation.
I tried expression, aggregator, joiner , union ..nothing seems to work. I cant get to this format that I need.
Yes , Like Niko mentioned , you would need a joiner transformation . Since SSN is the only common field in your example , join using SSN
Please note its always better to encrypt the values in the secure fields such as SSN .
Can you share a snapshot of your mapping .
Use 1 file as source and other file as Lookup and create Target as a new file.
I understand using joiner or lookup. My problem is the data itself. So the 1st column is header and 2nd column is data . The headers are in rows as is the data. I am used to column based tables, but never worked with row wise data. Can you tell me how to do that? Please see the example above in my original question.
Use normalizer transformation to fix the file data in generic form i.e. first line is header and 2nd line onwards data. once you fix that you can go for Lookup transformation as I suggested earlier.