This depends on the relations between the tables
There is my DataModel: (the table Host is linked to Data by the field host_key and the table ShadowRouter is linked to the table Data by the field Agent name)
left join (Data)
load [Agent name], Site_ShadowRouter as NewSite_ShadowRouter
left join (Host)
load host_key, NewSite_ShadowRouter
but this should works if you have 1 shadow router for host_key, and I don't know if this is true.
You can do the same with a nested applymap
If you post a small example with your data we can try.