De Duping , Count Of Records Analysis

[Masond3]

Afternoon Team, 

Hope all is well,

I am hoping you can help me. I have manually manipulated my data in excel and i am hoping i can leverage the power of Alteryx to help me automate this process going forward. 

In my Raw input File (Companies1) i have the following 4 columns 

Record Type

ID

Parent ID

Global EntityId

Example Input 

Record Type	ID	Parent ID	Global EntityId
EMEA	0013a00001k5ccbAAA	0013a00001f9VFuAAM	0013a00001f9VFuAAM
EMEA	0013a00001k5coyAAA	0013a00001f9VFuAAM	0013a00001f9VFuAAM
EMEA	0013a00001k5cbOAAQ	0013a00001f9VFuAAM	0013a00001f9VFuAAM
EMEA	0013a00001k763iAAA	0013a00001f9VFuAAM	0013a00001f9VFuAAM
EMEA	0013a00001f9VFuAAM		0013a00001f9VFuAAM

Step 1 - Produce a new tab which has the Global EntityID de duped 

Once we have a new tab and the de-duped Global EntityID ( we will use this de-duped list and reference company1 input file to produce the final calculation) 

Step 2 - Count how many times "Global entityid" on the new tab is in the "Companies1" Global EntityID Column 

Create a new field called "Total" and the output of this calculation will go in the new column 

Step 3 - Count how many times "Global entityid" on the new tab is in the "Companies1" Parent Column 

Create a new field called " # Of Parents" and the output of this calculation will go in the new column 

Step 3 - math equation "Total - # Of Parents". Put this output into a field called "Difference" 

Step 4 - produce an outcome column which is an if statement to produce following output 

If Total = 1 & Difference = 1 Then "Standalone"

If Total > 1 & Difference = 1 then "1 hierarchy"

IF difference > 1 then "Multi Level Hierarchy"

Expected Output File headers

Global EntityId

Total

# Of Parents

Difference

Outcome

Expected Outcome 

Global EntityId	Total	# Of Parents	Difference	Outcome
0013a00001f9VFuAAM	5	4	1	1 hierarchy

Looking forward to your help & Guidance 

[atcodedog05 · Accepted solution]

Hi @Masond3 

Really Sorry its raining here and internet was very bad so it happened.

Apologies.