I have two files
1. Investigation data file
2. Master file
From the investigation file, I have to take out RDM id and based on RDM Id take out the Entity Id. This Entity Id from the investigation file will then be used to lookup for data in the master file.
Investigation File Data
| Entity ID | RDM ID |
| 335812 | 1234 |
| 335897 | 5678 |
| 456789 | 12356 |
The Entity Id from Investigation file will be lookup with Primary Entity id in the Master file. The Primary Entity Id 456789 needs to be searched in Entity 1 id column to pull all the records with that id and this becomes level 1 in the output table. Then Entity Id 2 (567890) needs to be searched in Entity Id 1. The corresponding Entity id 2 (567891) becomes level 2 in the output table. Then Entity id (567891) needs to be searched in Entity Id 1. The corresponding Entity id 2 (567892) becomes level 3 in the output table. Then Entity id (567892) needs to be searched in Entity Id 1. The corresponding Entity id 2 (567893) becomes level 4 in the output table.
Master File Data
| Entity 1 id | client legal name | Entity 2 id | client legal name | Relationship type | Relationship Name | ShortCode | % ownership | Primary Entity ID | client legal name |
| 567892 | COL Ecstasy II Pte Ltd | 567893 | COL Asia III LP | 2 | Shareholder | SH | 100 | 456789 | COL WEALTH FINANCE LIMITED |
| 567891 | COL Ecstasy Pte Ltd | 567892 | COL Ecstasy II Pte Ltd | 2 | Shareholder | SH | 100 | 456789 | COL WEALTH FINANCE LIMITED |
| 567890 | COL WEALTH MANAGEMENT LIMITED | 567891 | COL Ecstasy Pte Ltd | 2 | Shareholder | SH | 100 | 456789 | COL WEALTH FINANCE LIMITED |
| 456789 | COL WEALTH FINANCE LIMITED | 567890 | COL WEALTH MANAGEMENT LIMITED | 2 | Shareholder | SH | 100 | 456789 | COL WEALTH FINANCE LIMITED |
| 456789 | COL WEALTH FINANCE LIMITED | 3457052 | K M Shiv | 1 | Director | D | 0 | 456789 | COL WEALTH FINANCE LIMITED |
| 456789 | COL WEALTH FINANCE LIMITED | 3457051 | Manish | 1 | Director | D | 0 | 456789 | COL WEALTH FINANCE LIMITED |
| 456789 | COL WEALTH FINANCE LIMITED | 3457050 | Nikhil | 1 | Director | D | 0 | 456789 | COL WEALTH FINANCE LIMITED |
| 456789 | COL WEALTH FINANCE LIMITED | 3457049 | Tushar Kumar | 1 | Director | D | 0 | 456789 | COL WEALTH FINANCE LIMITED |
| 456789 | COL WEALTH FINANCE LIMITED | 3457048 | Ramesh | 1 | Director | D | 0 | 456789 | COL WEALTH FINANCE LIMITED |
| 456789 | COL WEALTH FINANCE LIMITED | 3457047 | Birendra Kumar | 1 | Director | D | 0 | 456789 | COL WEALTH FINANCE LIMITED |
| 456789 | COL WEALTH FINANCE LIMITED | 3457046 | Ashish Kumar | 1 | Director | D | 0 | 456789 | COL WEALTH FINANCE LIMITED |
| 456789 | COL WEALTH FINANCE LIMITED | 3457045 | Anisha Teckchandani | 1 | Director | D | 0 | 456789 | COL WEALTH FINANCE LIMITED |
Output Table that needs to be built based on the data in the two files -
| Level | Id | Owner % | Relationship Name | Client Legal Name |
| 0 | 3457040 | | Client | COL WEALTH FINANCE LIMITED |
| 1 | 3457053 | 100 | Shareholder | COL WEALTH MANAGEMENT LIMITED |
| 2 | 3457054 | 100 | Shareholder | COL Ecstasy Pte Ltd |
| 3 | 3457055 | 100 | Shareholder | COL Ecstasy II Pte Ltd |
| 4 | 3457056 | 100 | Shareholder | COL Asia III LP |
| 1 | 3457052 | 0 | Director | K M Shiv |
| 1 | 3457051 | 0 | Director | Manish |
| 1 | 3457050 | 0 | Director | Nikhil |
| 1 | 3457049 | 0 | Director | Tushar Kumar |
| 1 | 3457048 | 0 | Director | Ramesh |
| 1 | 3457047 | 0 | Director | Birendra Kumar |
| 1 | 3457046 | 0 | Director | Ashish Kumar |
| 1 | 3457045 | 0 | Director | Anisha Teckchandani |
I have too many records with the above scenario but I am just posting one scenario for ease of building the workflow. All the records in the file need to run in a loop to check for the scenarios.
Any help here would be highly appreciated.
