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]

Hi @Masond3 

Here is a workflow for the task.

Output:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[atcodedog05]

Hi @Masond3 

I have updated the workflow just in case you have the old one by mistake.

[Masond3]

Hey,

So version of alteryx i am using is "Alteryx Designer 2019.3 x64

So when i download the flow i get the following message.

When i open it up i only get these part of the flow

Any idea ?

[atcodedog05]

Hi @Masond3 

Apologies. Its the old one before save.

Here is the updated and downgraded one

[Masond3]

HI 

Thanks for providing me this solution 

I am just vetting the results with a greater data set and i noticed and discrepancy with one the calculations in step 3 ( Number of Parents)

Using the current logic in the workflow it provides me with the following outcome ;

Global EntityId	Total	# Of Parents	Difference	Outcome
001j000000hdPneAAE	1447	1446	1	1 hierarchy

However the excepted outcome is ;

Global EntityId	Total	# Of Parents	Difference	Outcome
001j000000hdPneAAE	1448	27	1421	Multi Level hierarchy

The Calculation should Count how many times the Global EntityId is in the ParentID field 

Using the previous example and the latest example this should be the expected outcome

Global EntityId	Total	# Of Parents	Difference	Outcome
001j000000hdPneAAE	1448	27	1421	Multi Level hierarchy
0013a00001f9VFuAAM	5	4	1	1 hierarchy

I have attached the data set 

Looking forward to your help 

[atcodedog05]

Hi @Masond3 

Fixed it

Output

Please check and let me know🙂

[Masond3]

HI , 

So we are nearly there, however there is no standalone calculation (I assume this may need to be a separate flow due to the way you have done the join) 

Using the Latest example  Attached the  expected outcome should be as ; 

Global EntityId	Total	# Of Parents	Difference	Helper
0013a00001a3DPeAAM	1	0	1	Standalone
0013a00001f9VFuAAM	5	4	1	1 hierarchy
001j000000hdPneAAE	1448	27	1421	Multi Level hierarchy

However when i run it through the flow "standalone" is not in the output generated 

Global EntityId	Total	# Of Parents	Difference	Outcome
001j000000hdPneAAE	1448	27	1421	Multi Level hierarchy
0013a00001f9VFuAAM	5	4	1	1 hierarchy

[atcodedog05]

Hi @Masond3 

Here is the updated workflow

Output:

[Masond3]

Hi, 
@atcodedog05 
I think the flow attached is incorrect and maybe for another post ?