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.