Spoon lookup obtaining two rows instead of duplicate columns

Question

I have this two tables

If I use the lookup step using the EMPLOYEEID and YEAR I get this columns:

But I need to get this instead:

Answer 1

I have prepare a solution Here. Using this you will get your result.

Please let me know if its ok with you.

Answer 2

A lookup is designed to retrieve fields from a matching record in another table and append them to the "master" record.

If you want to combine records from 2 tables into a single dataset then you need to UNION them together.

Update following comment

Something like this pseudo-code ought to work:

SELECT T1.*
FROM TABLE1 T1
UNION
SELECT T2.*
FROM TABLE1 T2
INNER JOIN TABLE1 T1A ON T2.KEY1 = T1A.KEY1 AND T2.KEY2 = T1A.KEY2 AND ...