New Column Based On a Join & Conditions

[Masond3]

Hi Team, 

Hope your well .

I am hoping you can help me (My mind is drawing a blank) 

I have 2 input files which consists of the following ;

Input 1 

ID	Practice_ID
001j000000eKjdHAAS	123456

Input 2:

ID	Practice_ID
001j000000eKjdAAAS	123456
001j000000eKjdCAAS	123456
001j000000eKjdDAAS	123456
001j000000eKjdEAAS	123456
001j000000eKjdFAAS	123456
001j000000eKjdGAAS	123456
001j000000eKjdHAAS	123456
001j000000eKjdIAAS	123456
001j000000eKjdiAAC	123456

What i would like to do is following ;

1) Using Id in "Input 1" search for the Id in "Input 2"  and when the IDs match then in a new Column called "Outcome" tag the Record "HQ" 

2) Where the Remaining Ids & Practice Id doesn't match back to "input 1" then in the Outcome column tag "Office"

3) Produce a new Column called "Parent id"

4)Populate the HQ ID in the "Parentid" field where the HQ Practice_ID matches the other Practice_ID

NB : Where Outcome = HQ, Parentid should be blank  

Expected OutCome : 

ID	Practice_ID	Outcome	Parentid
001j000000eKjdAAAS	123456	Office	001j000000eKjdHAAS
001j000000eKjdCAAS	123456	Office	001j000000eKjdHAAS
001j000000eKjdDAAS	123456	Office	001j000000eKjdHAAS
001j000000eKjdEAAS	123456	Office	001j000000eKjdHAAS
001j000000eKjdFAAS	123456	Office	001j000000eKjdHAAS
001j000000eKjdGAAS	123456	Office	001j000000eKjdHAAS
001j000000eKjdHAAS	123456	HQ
001j000000eKjdIAAS	123456	Office	001j000000eKjdHAAS
001j000000eKjdiAAC	123456	Office	001j000000eKjdHAAS

Looking forward to your response 

Masond3

[mceleavey]

Hi @Masond3 ,

a little bit of joining and multi-row magic did the trick.

I've attached the workflow for you.

Hope this helps.

M.

[Masond3]

Hi @mceleavey 

Thanks for sending this across. 

Whilst testing your flow with some different scenarios ie additional Ids & Different Practice ID for the second input file, i have noticed that on the final output one parentid is being stamped for all the Offices 

I have re attached your flow with the example set i was testing with.

Regards
Masond3

[mceleavey]

Exactly.

What is it you wanted? There was only one in the original question.

Which one do you want to be stamped?

[mceleavey]

Sorry, got it.

I was being an idiot.

M.

[Masond3]

Hi @mceleavey 

For a given Practice id and the Outcome = Office, then the parentId field should be populated with the HQ id for that practice id 

For example

ID	Practice_ID	Outcome	Parent
001j000000eKga2AAC	18000062	HQ
001j000000eKincAAC	18000062	Office	001j000000eKga2AAC
001j000000eKgo7AAC	18000105	Office	001j000000eKjbJAAS
001j000000eKjbJAAS	18000105	HQ
001j000000eKjbIAAS	18000105	Office	001j000000eKjbJAAS
001j000000eKgocAAC	18000149	HQ
001j0000018keiqAAA	18000149	Office	001j000000eKgocAAC
001j000000cVKanAAG	18000169	Office	001j000000eKj7iAAC
001j000000eKj7iAAC	18000169	HQ
001j000000cVKS2AAO	18000172	Office	001j000000eKj7jAAC
001j000000eKj7jAAC	18000172	HQ
001j000000eKgosAAC	18000185	HQ
001j000000eKjbRAAS	18000185	Office	001j000000eKgosAAC
001j000000eKjbQAAS	18000185	Office	001j000000eKgosAAC
001j000000eKgovAAC	18000192	Office	001j000000yRSIdAAO
001j000000yRSIdAAO	18000192	HQ
001j000000eKgpuAAC	18000287	HQ
001f100001ZXkSrAAL	18000287	Office	001j000000eKgpuAAC
001j000000eKgq5AAC	18000309	Office	001f100001DOViHAAX
001f100001DOUe8AAH	18000309	Office	001f100001DOViHAAX
001f100001DOViHAAX	18000309	HQ
001j000000eKjbjAAC	18000309	Office	001f100001DOViHAAX
001j000000eKjbiAAC	18000309	Office	001f100001DOViHAAX
001j000000eKjbhAAC	18000309	Office	001f100001DOViHAAX
001j000000eKjbgAAC	18000309	Office	001f100001DOViHAAX
001f100001Ye0NQAAZ	18000309	Office	001f100001DOViHAAX
001j000000eKjdAAAS	123456	Office	001j000000eKjdHAAS
001j000000eKjdCAAS	123456	Office	001j000000eKjdHAAS
001j000000eKjdDAAS	123456	Office	001j000000eKjdHAAS
001j000000eKjdEAAS	123456	Office	001j000000eKjdHAAS
001j000000eKjdFAAS	123456	Office	001j000000eKjdHAAS
001j000000eKjdGAAS	123456	Office	001j000000eKjdHAAS
001j000000eKjdHAAS	123456	HQ

[AngelosPachis]

Hi @Masond3 ,

Based on M solution, can you try sticking the part inside the comment box in the workflow and see if that does it

It essentially filters out the "HQ" and then joins them back based on the Practice ID. Then the formula assigns the Parent ID.

Regards,

Angelos