Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Returning child records associated to parent and perform matching exercise

Masond3
8 - Asteroid

Hi Team,

 

Hope your well.

 

I am hoping you can help me or point me in the right direction. 

I would like to perform a fuzzy match / match that (compares two data sets against each other), but instead of running it across the whole data set, I would like the fuzzy match / match to only run for child records for a given parent

 

Hopefully the below explains my use case 

 

Input 1: Master 

Contains following 2 columns 

  • id
  • address
id address
11112 high street, new york, united states
2222101 central avenue, new york, united states

 

Input 2 : Data

Contains the following three columns;

  • Id
  • parent id
  • address 

 

Example data set ;

id parentid address
1111 1 high street, new york, united states
1111a11112 high street, new york, united states
1111b11113 high street, new york, united states
1111c11114 high street, new york, united states
2222 100 central avenue, new york, united states
2222a2222101 central avenue, new york, united states
2222b2222102 central avenue, new york, united states
2222c2222103 central avenue, new york, united states

 

Scenario :  1111

  1. return all child records from "data input" where parent = 1111
  2. should return records id 1111a,1111b,1111c
  3. take the address of record 1111 from input "master" (2 high street, new york, united states) and compare this  address against the child records of 1111a,1111b,1111c

 

Scenario :  2222

  1. return all child records from "data input" where parent = 2222
  2. should return records id 2222a,2222b,2222c
  3. take the address of record 2222 from input "master" (101 central avenue, new york, united states) and compare this  address against the child records of 2222a,2222b,2222c

What i would like to happen is that we are not comparing the address of "master" id 1111 against child records or 2222a,2222b,2222c, or "master" id 2222 against child records or 1111a,1111b,1111c

 

7 REPLIES 7
ArtApa
Alteryx
Alteryx

Hi @Masond3 - Is that something that you needed?

 

ArtApa_0-1620004297878.png

 

I attached both the app and the workflow.

Masond3
8 - Asteroid

@ArtApa Thank for sharing your solution. 

 

The first file yxwz doesn’t work on my version of Alteryx. Currently using version 19.3. 

Checking the second workflow although I am understanding the flow , however its not giving me the output i am expecting.  ( more than likely due to my description)

 

For example input "master" 

id address
11112 high street, new york, united states

 

Checking against the the "Data"  input we can see that there are 3 children for this particular parent

 

id 
1111a
1111b
1111c

 

Whilst comparing the addresses of the "master" vs the "child" input for the parent "1111" we can see that there is one child "1111a" where the addresses matches 100%. So it should return that as an outcome

 

id addresschild id child addressmatch %
11112 high street, new york, united states1111a2 high street, new york, united states100%

 

Lets say in the "master" input i have two addresses for parent 1111

 

id address
11112 high street, new york, united states
111195 highway avenue, Boston, united states

 

Whilst comparing the addresses of the "master" vs the "child" input for the parent "1111" we can see that there is one child "1111a" where the addresses matches 100%.  However the Boston address is not found in the list of children addresses

id 
1111a
1111b
1111c

 

the expected outcome should be something like this.. 

id addressid child addressmatch %
11112 high street, new york, united states1111a2 high street, new york, united states100%
111195 highway avenue, Boston, united states   

 

hope that makes more sense? more than happy to connect if you have further questions 

ArtApa
Alteryx
Alteryx

Hi @Masond3 - In your new scenario you don't need any Fuzzy logic. You'd use a Join to match 100% and a Union to bring the unmatched record back.

 

I guess it will be easier to offer you a solution, if you'd offer a data sample that is representative of the entire population of your data. We'd also need a sample of the desired output. 

Masond3
8 - Asteroid

@ArtApa 

 

I have attached the data set i am currently working with. On the data tab this is all child information. all i would like to do, is see if the address on the "Master" tab is in the "Child" Tab for a given Id 

 

My thinking is, if i can solve for this use case then it should hopefully meet all the uses cases. Why, as the below use cases has the following 3 scenarios ( and this most you can have ) 

 

  • Exact Match
  • Fuzzy Match
  • No match 

Input  : Master 

 

IDAddressCityState/ProvinceZip/Postal CodeCountry
1800303639 Jeays StreetBowen HillsQueensland4006Australia
18003036431-439 King William StreetAdelaideSouth Australia5000Australia
18003036367 Collins StreetMelbourneVictoria3000Australia
18003036Level 1 58 Burwood RoadHawthornVictoria3122Australia

 

Input : Data

 

IDParent ID Address (Line 1, Line 2, Line 3)CityState/ProvinceZip/Postal CodeCountry
001j000000eKjoPAAS18003036Level 1 431- 439 King William StreetAdelaideSouth Australia5000Australia
001j000000eKjoQAAS18003036Suite 11 39 Jeays StreetBowen HillsQueensland4006Australia
001j000000cVKUrAAO18003036367 Collins StreetMelbourneVictoria3000Australia
001j000000cVKT8AAO18003036Level 1 480 St Kilda RoadMelbourneVictoria3004Australia
001j000000eKh0KAAS18003036Unit 3 Level 2 10 Moorabool StreetGeelongVictoria3040Australia

 

Expected outcome 

 

IDAddressCityState/ProvinceZip/Postal CodeCountrychild id child address%Reason 
1800303639 Jeays StreetBowen HillsQueensland4006Australia001j000000eKjoQAASSuite 11 39 Jeays Street Fuzzy Match
18003036431-439 King William StreetAdelaideSouth Australia5000Australia001j000000eKjoPAASLevel 1 431- 439 King William Street Fuzzy Match
18003036367 Collins StreetMelbourneVictoria3000Australia001j000000cVKUrAAOLevel 31 367 Collins St Exact Match
18003036Level 1 58 Burwood RoadHawthornVictoria3122Australia   No Address matches 
Masond3
8 - Asteroid

If I think about it. What I'm looking to do is fuzzy match two sources , however I want the matching to only be executed within groups.

 

For example Alteryx to fuzzy match within Group a, then move on to B, then onto group c, d, etc 

ArtApa
Alteryx
Alteryx

Hi @Masond3 - The dataset helped. I designed something that I hope will meet your requirements:

 

ArtApa_0-1620110800338.png

 

Please note that your child data has 6 groups of similar addresses:

 

001j000000cVKPrAAO Level 22, ANZ Tower, 242 Pitt Street
001j000000heDOcAAM Level 23, 242 Pitt Street
001j000000eKjhiAAC 105 Mawson Drive
001j000000eKgcKAAS 105 Mawson Drive
001j000000eKjZSAA0 Level 4, 1 York Street
001j000000eKhEMAA0 Level 4, 1 York Street
001j000000cVKUlAAO Level 21, 25 Bligh Street
001j000000eKhGwAAK Level 21 25 Bligh Street
001j000000eKjaGAAS One International Towers, 100 Barangaroo Avenue
001j000000eKju3AAC One International Towers, 100 Barangaroo Avenue
001j000000eKiKZAA0 Suite 13, Edgecliff Mews, 201 New South Head Road
001f100001ZX5ESAA1 Suite 13,201 New South Head Rd

 

That is why the output has more records than input. You can use a Sample tool to pick just one or do something else, if you want to avoid this.

ArtApa
Alteryx
Alteryx

Hi @Masond3 - Can you please let me know if this worked for you? If so, please mark it as a solution. 

Labels