Child / Parent Relationship Count and Hierarchy Build

[Masond3]

Good Morning Community, 

I am hoping you can help me.

i am not sure how to describe my problem statement but hopefully with the attached and examples it will make sense 

In my input file i have 4 columns (See below), i would like to produce 2 different analysis based on those 4 Columns.

Tab : Data 

Company ID (Long) (Column A)

Parent Company ID (Long) (Column B)

Company Name (Column C)

Legal (Column D)

Outcome 1 : 

For a given Hierarchy determined by "Column A & "Column B count how many children are associated to the parent, and how many times is the Legal column field not null for that given hierarchy

NB : Please note some Company ID (Long) will not have parent and therefore is the parent in its own right

Outcome  :

Parent Company ID (Long)	Count of Children	Legal Count
001j000000hdRDpAAM	30	2

Outcome 2 :

Using the data from the Data tab, i have documented the current hierarchy (Column A - C). As you can see from this hierarchy it’s a very flat Structure. However within this Hierarchy two record  have been identified as  Legal.

Rules of the Road. 

• For a given relationship hierarchy if there is more than 1 in the legal count, a new record needs to be created at the top of the tree.
• For any record in that relationship hierarchy where the legal column is populated.
• I would like to create a new Parent record ( in my example the naming convention was "New Record -" + Company Name
• I would then like to link the legal Record (child) to the new legal parent Record.
• Once the Legal child / parent relationship is built, the Parent of the legal record would need to be linked to the top most parent.

 Example 

 "Mason Management Limited" is identified legal

Company ID (Long)	Parent Company ID (Long)	Company Name	Legal
001j000000hdPYZAA2	001j000000hdRDpAAM	Mason Management Limited	Y

Creating the Legal child parent relationship 

Name	Company ID (Long)	Legal
New Record - Mason Management Limited
Mason Management Limited	001j000000hdPYZAA2	Y

The final Hierarchy output is in column M-O

Looking forward to your help and response 

[atcodedog05]

Hi @Masond3 

The workflow is updated to add External ID

[atcodedog05]

Hi @Masond3 ,

I have modified the output. Now if Parent ID has less than 2 legal it will come down.

Output 2 : 

Output 3 : 

Hope this meet the requirement.

[Masond3]

This is pretty cool. I need to grasp this alteryx tool. But it can do a lot of call stuff . 

May i ask you an another stupid question ;

At this point in time we are using the parentid ( so direct parent of the child)

In my database i have the following three fields

Company ID (Long)
Parent Company ID (Long) ( is the direct parent) 

Global Entityid  ( this is the very top node , This id is stamped on all records, children and grand children etc)

As you can see from the screen shot below.  there are some instances where i have a parent, then a child, and then a grand child)

KingFisher Group
Company id = Populated 
parent id = Blank
Global Entityid = Company id

KingFisher EMEA 
Company id = Populated 
parent id = Populated with Kingfisher group Company id 
Global Entityid = Populated with Kingfisher group Company id 

KingFisher Uk
Company id = Populated 
parent id = Populated with Kingfisher EMEA Company id 
Global Entityid = Populated with Kingfisher group Company id 

KingFisher North America 
Company id = Populated 
parent id = Populated with Kingfisher Group
Global Entityid = Populated with Kingfisher group Company id 

If i only want to Look at records which only have parent and child, and no grandchild ( i would assume you need to use all those three fields to come o the conclusion if there any grand children )

Also i hit my limit of messages in private chat. 

[atcodedog05 · Accepted solution]

Hi @Masond3 ,

I have updated the workflow to show Parent ID.

Yes to consider only nodes which doesnt have grand child you can go with Filter isnull(Grand Child) or something similar.

Hope this helps : )