New Column Based On a Join & Conditions

Question

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

IDPractice_ID001j000000eKjdHAAS123456

Input 2:

IDPractice_ID001j000000eKjdAAAS123456001j000000eKjdCAAS123456001j000000eKjdDAAS123456001j000000eKjdEAAS123456001j000000eKjdFAAS123456001j000000eKjdGAAS123456001j000000eKjdHAAS123456001j000000eKjdIAAS123456001j000000eKjdiAAC123456

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 :

IDPractice_IDOutcome Parentid 001j000000eKjdAAAS123456Office001j000000eKjdHAAS001j000000eKjdCAAS123456Office001j000000eKjdHAAS001j000000eKjdDAAS123456Office001j000000eKjdHAAS001j000000eKjdEAAS123456Office001j000000eKjdHAAS001j000000eKjdFAAS123456Office001j000000eKjdHAAS001j000000eKjdGAAS123456Office001j000000eKjdHAAS001j000000eKjdHAAS123456HQ 001j000000eKjdIAAS123456Office001j000000eKjdHAAS001j000000eKjdiAAC123456Office001j000000eKjdHAAS

Looking forward to your response

Masond3

AngelosPachis · Answer

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

Solution3.yxmd

Masond3 · Answer

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

IDPractice_IDOutcomeParent001j000000eKga2AAC18000062HQ 001j000000eKincAAC18000062Office001j000000eKga2AAC001j000000eKgo7AAC18000105Office001j000000eKjbJAAS001j000000eKjbJAAS18000105HQ 001j000000eKjbIAAS18000105Office001j000000eKjbJAAS001j000000eKgocAAC18000149HQ 001j0000018keiqAAA18000149Office001j000000eKgocAAC001j000000cVKanAAG18000169Office001j000000eKj7iAAC001j000000eKj7iAAC18000169HQ 001j000000cVKS2AAO18000172Office001j000000eKj7jAAC001j000000eKj7jAAC18000172HQ 001j000000eKgosAAC18000185HQ 001j000000eKjbRAAS18000185Office001j000000eKgosAAC001j000000eKjbQAAS18000185Office001j000000eKgosAAC001j000000eKgovAAC18000192Office001j000000yRSIdAAO001j000000yRSIdAAO18000192HQ 001j000000eKgpuAAC18000287HQ 001f100001ZXkSrAAL18000287Office001j000000eKgpuAAC001j000000eKgq5AAC18000309Office001f100001DOViHAAX001f100001DOUe8AAH18000309Office001f100001DOViHAAX001f100001DOViHAAX18000309HQ 001j000000eKjbjAAC18000309Office001f100001DOViHAAX001j000000eKjbiAAC18000309Office001f100001DOViHAAX001j000000eKjbhAAC18000309Office001f100001DOViHAAX001j000000eKjbgAAC18000309Office001f100001DOViHAAX001f100001Ye0NQAAZ18000309Office001f100001DOViHAAX001j000000eKjdAAAS123456Office001j000000eKjdHAAS001j000000eKjdCAAS123456Office001j000000eKjdHAAS001j000000eKjdDAAS123456Office001j000000eKjdHAAS001j000000eKjdEAAS123456Office001j000000eKjdHAAS001j000000eKjdFAAS123456Office001j000000eKjdHAAS001j000000eKjdGAAS123456Office001j000000eKjdHAAS001j000000eKjdHAAS123456HQ

mceleavey · Answer

Sorry, got it.

I was being an idiot.

M.

Solution2.yxmd