Family tree analysis - Network sequence

Question

Hello

I'm currently working on a new project where i'm analyzing a family tree, and one of the exercises that i'm currently working on is getting the order of the family tree. it's a huge data set and i'm usually asked about an information for a specific pair, (e.g the 200 pair). I only know the first pair (e.g. in the file attached, Parent: Khaled and Child: Mohammad).

I tried to use alteryx to find the sequence in multiple ways; i've tried joining the data set on each other multiple times to get the pair intended but this is unpractical way if i want the 200 pair for example. I've also tried fuzzy matching as an alternative but this also failed.

I've attached a sample of the database I have, I would really appreciate your help in this.

Family Tree .xlsx

NicoleJohnson · Accepted Answer

If I understand your question correctly, I think you might be able to achieve this with an iterative macro. Would essentially take your first pair (Khaled & Mohammad) and then match Mohammad to the Parent column to find Mohammad's child... and then repeat for the rest of the data in the Family member data set. The output will show pair #, which can be joined back to your original pair to get the full list, at which point you could filter for a specific pair #.

However, I can anticipate potential issues if your data set contains more than one child per parent... is this the case? Further tweaking could be required, but achievable if you build in some sort of logic that would assign pair #'s sequentially for any additional children, and then start with the next sequential number for the following iteration... let us know if you might have situations like that in your data set that would need to be accounted for!

EDIT: Actually, I just tested this, and you might be alright with siblings IF you don't mind having more than one pair at your 200th level... i.e. you will have multiple pairs that show up as the 200th level (potentially MANY multiple pairs) depending how far your tree data goes back along each branch)... but if this is okay, then the workflow will still function correctly, with one more caveat... this will only work if each person has a unique name or other identifier, otherwise you might end up being your own grandpa :)

Hope that helps get you started...

Cheers,

NJ