Append fields of the table and Also Add a Column with Table Name

Question

Table 1 -:Employee Name, Salary    Aayush, 40000Rajiv,30000Table 2 -: 
Employee Name1, Salary1    Mohit,70000Aman,80000Output -:Source, Employee Name, Salary, Employee Name1, Salary1
Table1, Aayush, 40000, Null, Null
Table1,Rajiv, 30000, Null, Null
Table2,Null, Null, Mohit, 70000
Table2, Null, Null, Aman, 80000

Is there any tool which can help me get the desired results.

TUSHAR050392 · Answer

Hey @prakhar021 Is that how your data looks - separated by comma? If yes then you need to get it into columns as mentioned by @OTrieger else directly use below solution. Once you have it into columns you can use another approach.

Use formula tool in both tables and name a new column called source and just mention 'Table1' and 'Table2'. After that Union both the datasets by name and you will get null wherever there is no match. After that if you want in that comma separated format just use a formula tool and create a new column with formula - Source + ',',+ Employee Name + ',' + Salary + ',' +  Employee Name1 + ',' + Salary1 and drop other columns using select.

OTrieger · Answer

Hi @prakhar021

Right now all your data is comma delaminated.
Use Text To Column Tool to get the data into Columns, each of the files, Add a Dynamic renaming from first row and then use a union tool. With Multi Field formula add text Null if field is null. Then you can use Transpose and Cross Tab it back.