I have a problem where there is a bunch of data being mapped from one table, 'clients' to the main 'allocations' table on 'Client ID'. Sometimes this mapping results in a Client Name of 'Unallocated'. I have the names of the six different client id's that result in this.
What I have to do is, in the circumstance that the mapping gives 'Unallocated', instead map to another data source, 'Counterparties', but this time matching on 'SIC'.
Ideally, this would be done in a way where the 'allocations' table is not reloaded at all, as it contains a huge amount of data and can currently optimized load.
One way I thought might work would be to do a conditional mapping on the Client data, changing it to that from the Counterparties source in the case where the client id is one of the six bad ones. I would then have to change the allocations table with a conditional mapping to change the Client ID to be the SIC instead. The trouble is this goes against what I've just said about reloading the allocations table and is incredibly slow.
Is there any way I can easily merge the Clients and Counterparties data into one entity, and have the Allocations match on either SIC or Client ID without creating a synthetic key? I cant seem to work this one out.
