Using 2 input files to find child records to produce new Output

[Masond3]

Hello All, 

Simple ask but i believe its achievable 

I have an 2 input files.

Input 1  : contains these 4 columns 

Record Type

ID

Parent ID

Ultimate Parent id

Input 2 : contains this 1 column 

ID

Goal : 

• Any  ID within Input 2 search within the  "Parent Id" in input 1
• return all Ids (from input 1)  where the  "ParentId" = id (from input 2)  
• Produce a new table with corresponding ID 

Source_Company__c

Target_Company__c

 "Source_Company__C"  = Id from Input 2
 "Target_Company__c" = Id from Input 1 where parentid = Id of input 2

NB : there might be scenarios where the ID from input 2, may not exist in Parentid input 1. If this is the case in the output file there shouldnt be any records where target_Company_c is blank 

Data Mock up Input 1 

Record Type	ID	Parent ID	Global EntityId
APAC	0013a00001gELT3AAO		001j000000heNBNAA2
EMEA	0013a00001gDCVHAA4	001j000000heNBNAA2	001j000000heNBNAA2
EMEA	001j000000heO9iAAE	001j000000heNBNAA2	001j000000heNBNAA2
APAC	001j000000heO9gAAE		001j000000heNBNAA2
EMEA	001j000000heO9YAAU	001j000000heNBNAA2	001j000000heNBNAA2
EMEA	001j000000heO9QAAU	001j000000heNBNAA2	001j000000heNBNAA2

Data Mock up Input 2

ID

001j000000heNBNAA2

Expected outcome 

Source_Company__c	Target_Company__c
001j000000heNBNAA2	0013a00001gDCVHAA4
001j000000heNBNAA2	001j000000heO9iAAE
001j000000heNBNAA2	001j000000heO9YAAU
001j000000heNBNAA2	001j000000heO9QAAU

Looking forward to your help 

[atcodedog05]

Hi @Masond3 

This can be can achieved by a join tool. Let me take a look.

[atcodedog05 · Accepted solution]

Hi @Masond3 

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Masond3]

Thank you buddy, 
Swift and efficient 🙂