Talend Lookup: Retrieving Client IDs Based on Client Names

Question

I have the following tables :

Sale ID,Sale Amount,Client 1,Client 2
1,500,John,Alice
2,700,Bob,Sarah
3,300,Alice,Michael
4,900,Sarah,Bob


Client Name,Client ID
John,100
Alice,101
Bob,102
Sarah,103
Michael,104

my goal is to map each client to its ID . but each sale has two clients . how to perform multiple lookups based on the same column

the result should be :

SaleId, Client1Id,Client1Id
1,100,101
2,102,103
3,101,104

Reading the ID file two times isn't an optimal solutions .

Answer 1

Use (in a tPreJob) a tHashOutput to load the ClientName-ClientId pairs in memory. Then link it a tHashInput to do the mapping when you need it.

You may need to add these components to the Palette first.

and then