Identifying new Records to Create when comparing against Addresses records

[Masond3]

Afternoon Community, 

Hoping you can help me.

I know the flow but just struggling to think how i could get this to work within alteryx 

I have attached some sample data to help explain my use case.

I have two input files ;

• Company 
• Contact

The aim of the exercise is to the following ;

1. Map contacts to the correct "Office"
2. Only search for those offices in the given hierarchy for a "Practice_Id" 
3. Flag new Office records which need to be created where contact address doesn't match office address for a practice_Id hierarchy 

Steps : 

1. Find "Adviser_Practice_ID" against "Practice_Id" where "Level" = Company 
2. Return all child records where Level = "Office" & "Parent id" = Id of the company id above
3. Compare Contact Address vs Company address where the address matches then return "Company ID" 
4. Compare Contact Address vs Company address where the address does not match then return Blank

Contact Input data

Adviser_Practice_ID	Building	Address	City	State	Postcode
18000009	Suite 5	2-6 Albert St	Blackburn	Victoria	3130
18000009	Suite 5	2-6 Albert St	Blackburn	Victoria	3130
18000009	Suite 5	2-6 Albert St	Blackburn	Victoria	3130
18000009	Suite 1	128 Jolimont Rd	East Melbourne	Victoria	1111
18000009	Suite 5	2-6 Albert St	Blackburn	Victoria	3130

Company Input data 

Practice_ID	Name	Building	Address	City	State	Postcode	Level	Company id	Parent id
18000009	Mason LTD	Suite 5	2-6 Albert St	Blackburn	Victoria	3130	Office	001j000000eKhoRAAS	0013a00001ndVPtAAM
18000009	Mason LTD	Suite 5	2-6 Albert St	Blackburn	Victoria	3130	Company	0013a00001ndVPtAAM
	Mason LTD	Suite 217, Toorak Business Centre	19 Milton Parade	Malvern	Victoia	2952	Office	0013a00001ndflGAAQ	0013a00001ndVPtAAM
18002199	Amber Insurance		1 High Street	Toorak	Victoria	3142	Office	001j000000eKh8dAAC	0013a00001ndXIwAAM
18002199	Amber Insurance		1 High Street	Toorak	Victoria	3142	Company	0013a00001ndXIwAAM

Expected out come 

Adviser_Practice_ID	Building	Address	City	Adviser_State	Postcode	Company ID	Helper
18000009	Suite 5	2-6 Albert St	Blackburn	Victoria	3130	001j000000eKhoRAAS	Map To Existing Company ID
18000009	Suite 5	2-6 Albert St	Blackburn	Victoria	3130	001j000000eKhoRAAS	Map To Existing Company ID
18000009	Suite 5	2-6 Albert St	Blackburn	Victoria	3130	001j000000eKhoRAAS	Map To Existing Company ID
18000009	Suite 1	128 Jolimont Rd	East Melbourne	Victoria	1111		Create New Company
18000009	Suite 5	2-6 Albert St	Blackburn	Victoria	3130	001j000000eKhoRAAS	Map To Existing Company ID

Looking forward to your help 

Regards
Masond3

[RaviP]

Interesting problem @Masond3.

Let me know if the attached solution would help.

Ravi

[Masond3]

@RaviP 

Thank you for taking your time to have a look at this. 

Whilst checking i have found some items;

1) An advisor can only ever be associated to records where level = "Office". In the final output we currently have;

Helper = "Map to existing company id" 

Company id = "0013a00001ndVPtAAM"

Level = Company  

when it should be 

Helper = Map to existing company id" 

Company id = "001j000000eKhoRAAS"

Level = Office

2) I need another column which will be the Parent Id (Level = Company) of the Office 

For example advisor practice id 18000009

Helper = Map to existing company id"

Parent Id =0013a00001ndVPtAAM

Helper = Create new Company

Parent Id =0013a00001ndVPtAAM

I like what you have done with the concatenation of address , as addresses can be tricky, do you think we should enhance and run a fuzzy matching logic to find address matches and set a threshold ? 

For example 
Lets say in the company input file i have the following ;

126 Jolimont Rd

East Melbourne

Victoria

1111

and in the contact file i have; 

Suite 1 

128 Jolimont Rd

East Melbourne

Victoria

1111

We currently doing a concatenation  of the full address and doing a join. As these 2 records dont match it would , it wouldn't return in the result set. If we applied a fuzzy match, you may find that we have a 98% hit rate, and we should join these two records together as they are pretty similar 

[RaviP]

Hi @Masond3 

I haven't really used the fuzzy match. I've tried to see what I can do with the example you gave me. It works even if there's a null value in building. Not sure if this will work if there missing values in other columns.

Don't think my solution is particularly effective, but it works.

Regards,

Ravi

[Masond3]

@RaviP  - I tried to understand your flow and i am little confused. 
How do you ensure you are only comparing the child records (offices) for a given parent (Company)  and not the entire data set ? 

[RaviP]

Hi @Masond3 , apologies for the delay. 

I've tried to answer your and added a few comments. Let me if this is fine