Multiple Joins Logic

Question

Hi Team,

Need your help and support on building below mention query.

I am trying to join the data by using Join functionality - need community support to built below logic - Thanks in advance.

Attaching excel file consist two sheets FileA and FileB - trying to join both the sheets by using below logic

1) FileA: ID+SM field need to match with ID+Alternative Identifier in FileB (No direct field in FileB need to concatenate ID+Alternative Identifier) if not then,

2) FileA: ID+SM field need to match with ID+FT SECID if not then,
3) FileA: ID+SM field need to match with ID+LM SECID if not then,
4) FileA: ID+SM field need to match with ID+Cusip  if not then,
5) FileA: ID+SM field need to match with ID+Sedol  if not then,
6) FileA: ID+SM field need to match with ID+Isin  if not then ... still if any pending unjoins i need to see those in separate sheet,Thanks.

Join Logic.xlsx

CWG0816 · Accepted Answer

This can be achieved by using the join tool using ID and SM as separate field identifiers and then ID and the second matching variable.

The attached worklfow method is joining only those records which did not match in the prior tool. It is creating a trickle down filter. If you wanted File A or B to be matched against the entire file then the input side of each Join tool should come from the Select tool after the input you wish to keep whole.

Also, I would recommend attempting a workflow and posting it with your data source - It can help assess your goal and perhaps the area needed to develop the workflow yourself.

Chris