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
